Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I created a small sales volumen table (fact table), this table is linked to order date table (dimension table). I then created a measure as below, which works normally.
@Anonymous Would need to understand your source data and expected output but yes, ALL can dramatically alter a calculation because you are essentially ignoring all filter context that have been placed on that calculation like column you are in, row you are in, any visual filters, etc.
Here is the sales table.
Order DateShip Datecategorysales amt
2019-01-01 | 2019-01-01 | coffee | 3 |
2019-01-01 | 2019-02-01 | tea | 2 |
2019-01-01 | 2019-02-01 | popcorn | 3 |
2019-01-01 | 2019-02-01 | chocolate | 4 |
2019-02-01 | 2019-02-01 | coffee | 4 |
2019-02-01 | 2019-02-01 | tea | 4 |
2019-02-01 | 2019-02-01 | popcorn | 4 |
2019-02-01 | 2019-02-01 | chocolate | 4 |
2019-03-01 | 2019-03-01 | coffee | 4 |
2019-03-01 | 2019-03-01 | tea | 4 |
2019-03-01 | 2019-03-01 | popcorn | 4 |
2019-03-01 | 2019-03-01 | chocolate | 4 |
2019-04-01 | 2019-04-01 | coffee | 4 |
2019-04-01 | 2019-04-01 | tea | 4 |
2019-04-01 | 2019-04-01 | popcorn | 4 |
2019-04-01 | 2019-04-01 | chocolate | 4 |
It's linked to 'order date' table via 'order date' column. Below is a screen shot of 'order date' table.
I found the reason. I have created a new column called sales vol Juanary3. I meant to create a new measure, which is clearly not the case. I should have checked the fields panel more carefully.
@Anonymous Even if you author a measure as such, the result still unreasonable.
As the FILTER(ALL(order date), ... ) returns a table with a column 'order date'[order month]="2019-01", it overrides all other filter context from the matrix, say [order month]="2019-02", "2019-03"..."2019-12", thus every row of matrix will be filled with sum(sales[sales amt]) of "2019-01".
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |