Search found 919 matches

by MattAllington
Wed Feb 14, 2018 6:18 pm
Forum: PowerPivot/DAX
Topic: duplicates
Replies: 3
Views: 115

Re: duplicates

Maybe you could be a bit more specific?
by MattAllington
Mon Feb 05, 2018 6:35 am
Forum: General
Topic: Error Message
Replies: 2
Views: 162

Re: Error Message

What version of Excel? Excel 2016 is the most risky (I always keep incremental backups)

Read my article here Power Pivot Crashes
Maybe also try migrating the data model to Power BI as a way of saving it
by MattAllington
Thu Feb 01, 2018 7:18 am
Forum: PowerPivot/DAX
Topic: Measure to Sum the Lower Levels.
Replies: 2
Views: 179

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: 93

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: 187

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: 2
Views: 279

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: 465

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: 465

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: 532

Re: Context transition in measures

Yes I saw it too, and almost posted it here too

Go to advanced search