Search found 1062 matches

by MattAllington
Thu Oct 10, 2019 9:21 pm
Forum: Help with the Book "Supercharge Power BI"
Topic: Practice Exercise 60
Replies: 2
Views: 45

Re: Practice Exercise 60

The above is perfectly fine. In this case it is the same, in other cases this won’t work. Eg what are the total sales of these products? In that case you need to keep the filter and use calculate.
by MattAllington
Tue Oct 08, 2019 7:12 pm
Forum: PowerPivot/DAX
Topic: Accruals measure
Replies: 1
Views: 42

Re: Accruals measure

Well it can be done, but every DAX formula depends on the table structure. I suggest you create a meaningful set of realistic test data an share it along with your expected outcome.
by MattAllington
Tue Oct 08, 2019 7:11 pm
Forum: PowerPivot/DAX
Topic: Relationships, Power Pivot vs Access
Replies: 2
Views: 99

Re: Relationships, Power Pivot vs Access

These relationships are not a 1 for one substitute although I understand why you are asking. In short, you should prefer a star schema for the data loaded in power pivot. Read here https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/ You should not be aiming to simply replicate relat...
by MattAllington
Sat Oct 05, 2019 7:53 am
Forum: Power Query
Topic: What does orange colour icon means?
Replies: 1
Views: 290

Re: What does orange colour icon means?

It means the table has been loaded to the power pivot data model. There is indeed 2 copies of the table.

https://exceleratorbi.com.au/what-is-power-pivot/
by MattAllington
Sat Oct 05, 2019 7:50 am
Forum: Power Query
Topic: Delete Add Tables from Power Query
Replies: 1
Views: 271

Re: Delete Add Tables from Power Query

Are these tables loaded in Power Pivot? If so, go to Excel, open the queries pane, right click on the query you want to remove, select “load to” and then deselect “add to data Model”
by MattAllington
Sat Oct 05, 2019 7:48 am
Forum: Power Query
Topic: dealing with Excel #N/A
Replies: 1
Views: 40

Re: dealing with Excel #N/A

Have you tried replacing the NA using power query before any “changes type” steps?
by MattAllington
Sun Sep 22, 2019 6:43 pm
Forum: PowerPivot/DAX
Topic: Sum of Maximum Values
Replies: 2
Views: 193

Re: Sum of Maximum Values

Your un-pivoting is good - well done. You really should now build a star schema. This makes the dax easier to write and the model more efficient. It looks like you need an employee table (lookup table) with the role, id, function, location etc. Just leave the role ID in the data table. Then you need...
by MattAllington
Sat Sep 21, 2019 4:19 pm
Forum: PowerPivot/DAX
Topic: Two Calculations in a Dax Formula Based on Another Field
Replies: 1
Views: 120

Re: Two Calculations in a Dax Formula Based on Another Field

All DAX formulas depend on the table structure. You dont say if you want these totals additive, but I assume you do. You would need something like =sumx( values(table[trainer/non trainer column name]), if(max(table[trainer/non trainer column name])=“Trainer”, calculate(sum(table[pay])/100, calculate...
by MattAllington
Sat Sep 21, 2019 4:08 pm
Forum: PowerPivot/DAX
Topic: Only Fact Table on the Model [Solved]
Replies: 2
Views: 454

Re: Only Fact Table on the Model [Solved]

Yes you can. You can use a single flat table if you want. The best design is a star schema. Generally it is the best trade off for compression, efficiency and simplicity With the DAX.
by MattAllington
Sun Jul 07, 2019 9:27 am
Forum: PowerPivot/DAX
Topic: Lost Customers TY vs LY
Replies: 5
Views: 449

Re: Lost Customers TY vs LY

Well it is a non trivial task requiring a non trivial solution. I have copied this code myself and it just worked.

Go to advanced search