Filter rows by standalone lookup date table
I've written a DAX measure to give me number of cases that match a lookup of certain base date.
My dax measure is in the DaxMeasure.PNG file.
It works mostly, but I'm having difficulties showing me the total when I filter by certain date.
When I filer by the date 20.03.2019 it gives me the desired result, that is, all the rows that this date falls between, and the grand total that is distinct count of case SQN number (file FilterScenario1.png)
When I on the other hand filter by another date, 19.03.2019 it gives me the desired result, but there is no Grand total showing (file FilterScenario2.png)
(I've tried both Excel and Power BI and it's giving me the same result.)
Any idea what is causing this, is it the maybe the blanks in my DAX query or something else, any help appreciated.
Cheers,
Gosi
DaxMeasure.PNG
FilterScenario1.png
FilterScenario2.png
My dax measure is in the DaxMeasure.PNG file.
It works mostly, but I'm having difficulties showing me the total when I filter by certain date.
When I filer by the date 20.03.2019 it gives me the desired result, that is, all the rows that this date falls between, and the grand total that is distinct count of case SQN number (file FilterScenario1.png)
When I on the other hand filter by another date, 19.03.2019 it gives me the desired result, but there is no Grand total showing (file FilterScenario2.png)
(I've tried both Excel and Power BI and it's giving me the same result.)
Any idea what is causing this, is it the maybe the blanks in my DAX query or something else, any help appreciated.
Cheers,
Gosi
DaxMeasure.PNG
FilterScenario1.png
FilterScenario2.png
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Looking at your DAX, IsVisible is True when either the date dimension is not filtered (which it appears to be in both of these cases) or where the date you've selected is between the MAX From and To dates for your set of transactions. Something about this evaluation must be returning False when it's run for the whole filtered dataset in the second example, but that's not true in the first example.
If you add a measure to just evaluate IsVisible on its own and return that, what do you get? Is it True for teh single transaction but then False for the whole dataset (grand total)?