Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 2 DAX measures:
Backlog (h) =
CALCULATE (
SUM ( 'dw VW_WorkOrderFactuom'[Work Order Duration Remaining (h)] ),
'dw VW_WorkOrderFactUOM'[In Backlog] = TRUE (),
'dw VW_WorkOrderFactUOM'[Live Backlog] = TRUE (),
NOT ( 'dw WorkOrderDim'[Work Purpose] IN { "MO", "WS", BLANK () } )
)
Backlog (h) 7 Days Ago NEW =
CALCULATE ( [Backlog (h)], DATEADD ( 'dw DateDim'[Date], -7, DAY ) )
1. Shows 'Backlog (h)' on the December 3 = 51,293
2. Shows 'Backlog (h)' 7 days previous on November 26 =52,602
3. Shows 'Backlog (h) 7 Days Ago NEW' = 52,602
Everything is working as expected.
If I select the grid row for December 3:
1. Shows 'Backlog (h)' = 51,293
2. Shows 'Backlog (h) 7 Days Ago NEW' = 52,602
Everything is working as expected
If I use my Date Slicer to select only December 3rd:
1. Shows Date Slicer December 3rd selected
2. Shows Backlog (h) = 51,293
3. Shows 'Backlog (h) 7 Days Ago NEW' as Blank
'Backlog (h) 7 Days Ago NEW' breaks when I use a date slicer, why?
I believe there is something wrong with the DAX in 'Backlog (h) 7 Days Ago NEW' that breaks when I use my date slicer. I'm thinking it has something to do with filtering and could require am 'ALL', 'ALLSELECTED' or 'ALLEXCEPT' type of function.
Any help is greatly appreciated.
Steven
Solved! Go to Solution.
If you mark the 'dw DateDim' table as the date table in the model, it should all work. Have you marked the table as such? I can see that you join the tables on DimDateKey which I believe is an int key. In this case you have to mark the table as a DATE table and select a true date column as the key in it. Then everything will be working fine.
If you mark the 'dw DateDim' table as the date table in the model, it should all work. Have you marked the table as such? I can see that you join the tables on DimDateKey which I believe is an int key. In this case you have to mark the table as a DATE table and select a true date column as the key in it. Then everything will be working fine.
Thanks for a great solution. Much appreciated
Hi, @Anonymous , indeed, the reason for the issue lies in the hierarchical date slicer; and it's truly hidden. But it's revealed here (hover the mouse on the funnel icon of table viz)
As to your measure,
Backlog (h) 7 Days Ago NEW =
CALCULATE ( [Backlog (h)], DATEADD ( 'dw DateDim'[Date], -7, DAY ) )
DATEADD() evaluates to 'dw DateDim'[Date]=2020/11/26, which overrides 'dw DateDim'[Date]=2020/12/3 (that's why all works when the 'dw DateDim'[Date] is used as slicer at the top level); but another 2 filters still lingering there unexpectedly, as shown above, 'dw DateDim'[Year]=2020 and 'dw DateDim'[Month Name]=Dec!
The measure only evaluates with all these filters together; unfortunately these filters filter out all dates down to blank.
Enjoy filters, enjoy DAX! 😁
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! |
Excellent explanation of the problem. Thanks for your help 🙂
Hi, @Anonymous , it's not surprising such seemingly same slicing brings about different results as measures are super sensitive to any changes in the evaluation context.
I doubt that the date columns in the table viz and the slicer are the same. In fact, it requires to take a close look at the data model and vizs to troubleshoot.
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! |
Hi CNENFRNL,
Thanks for the prompt reply it is much appreciated. I am including screenshots of the relationship between my DateDim table and my WorkOrderFactUOM table:
Screenshot of my table viz using 'dw DateDim'[Date]
Screenshot of hierarchical slicer using 'dw DateDim'[Date], 'dw DateDim'[Year No] and 'dw DateDim'[Month Name]
Screenshot of a Non hierarchical slicer using 'dw DateDim'[Date]. Bizararrely, this works fine. It appears to be an issue when using the hierarchy slicer
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |