Search found 947 matches

by MattAllington
Thu Feb 01, 2018 7:18 am
Forum: PowerPivot/DAX
Topic: Measure to Sum the Lower Levels.
Replies: 2
Views: 900

Re: Measure to Sum the Lower Levels.

The issue is that you can't complete the calculation you are trying to do at the aggregate level. You need to do it line by line.

This should work =sumx(VALUES(Table1[Level 2 Prodcut]),([ASP 2a]-[ASP 1a])*[Qty 1])

To understand why, read my article SUM vs SUMX Explained
by MattAllington
Thu Feb 01, 2018 6:44 am
Forum: PowerPivot/DAX
Topic: Text to Decimal
Replies: 1
Views: 575

Re: Text to Decimal

Well you can't load data into a column in Power Pivot that has text and numbers AND have it marked as a numeric column. Same with any database actually. But DAX is a very forgiving language. If you were to add a Calculated Column to your table something like =IFERROR(VALUE(Table1[Text]),BLANK()) you...
by MattAllington
Mon Jan 22, 2018 7:45 am
Forum: PowerPivot/DAX
Topic: Quotas
Replies: 2
Views: 859

Re: Quotas

Do you mean like a budget? You can simply load another table. I have a few articles
here https://exceleratorbi.com.au/multiple-d ... wer-pivot/
and here https://exceleratorbi.com.au/virtual-fi ... g-treatas/
by MattAllington
Fri Jan 12, 2018 8:41 am
Forum: Power Query
Topic: Data Normalization
Replies: 3
Views: 1355

Re: Data Normalization

⋅ connect to the source data ⋅ set this query so it doesn't load. In PBI, right click and turn off "enable load". In Excel, "close and load", "Connection only" ⋅ go back into power query, right click on the above query, select "refere...
by MattAllington
Fri Jan 12, 2018 8:17 am
Forum: Power Query
Topic: Slowly Changing Dimension Type 2
Replies: 7
Views: 1481

Re: Slowly Changing Dimension Type 2

I'm not really sure what you mean wrt the new data. I assume you are saying your fact table will have a single date column and somehow it needs to map to the start/end dates your SCD. Is that correct? If this is correct and you want to load all these SCD records to your datamodel, then the SCD is ac...
by MattAllington
Thu Jan 11, 2018 7:11 am
Forum: Power Query
Topic: Slowly Changing Dimension Type 2
Replies: 7
Views: 1481

Re: Slowly Changing Dimension Type 2

OK, it came to me when I was taking a walk late yesterday. I have used the technique I posted in my video above twice. The first time I used it to map the current record to the next record and remove any class records that didn't change (this what the problem I had yesterday). After removing these s...
by MattAllington
Thu Jan 11, 2018 6:55 am
Forum: PowerPivot/DAX
Topic: Context transition in measures
Replies: 13
Views: 2144

Re: Context transition in measures

Yes I saw it too, and almost posted it here too
by MattAllington
Wed Jan 10, 2018 3:01 pm
Forum: Power Query
Topic: Slowly Changing Dimension Type 2
Replies: 7
Views: 1481

Re: Slowly Changing Dimension Type 2

OK, so this is more like this video I produced some time ago https://www.youtube.com/watch?v=xN2IRXQ2CvI

I have had a go but couldn't get it to do what I needed. Sorry, I am out of time. This may be a good one for Imke. I will ask her if she has time to take a look.
by MattAllington
Tue Jan 09, 2018 8:45 am
Forum: PowerPivot/DAX
Topic: Context transition in measures
Replies: 13
Views: 2144

Re: Context transition in measures

Hi Marc

Very good, deep questions and very well "asked". It shows your DAX skill is strong and you will only get stronger.

can you please post your sample workbook. I will then write a response as a blog post and let you know when it is done.

Matt

Go to advanced search