The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi all,
I have a data table that includes these columns:
Name | Due Date | Completed Date |
I also have a calendar table ('Calendar Lookup') that feeds a single-select slicer with the month-end date.
I want to show a status based on the difference of Due Date and Slicer Date. I believe because I'm using the slicer date and not a static date, that I can't do anything with a DAX column and must use a measure.
I've created a grouping table ('Grouping Lookup'):
And I'm currently using this measure to determine the status:
Due Status =
VAR Overdue =
CALCULATE(
[Total items],
DATEDIFF('Data Table'[Due Date], MAX('Calendar Lookup'[Date]), DAY) > 0
)
VAR DueSoon =
CALCULATE(
[Total items],
DATEDIFF('Data Table'[Due Date], MAX('Calendar Lookup'[Date]), DAY) > -7 &&
DATEDIFF('Data Table'[Due Date], MAX('Calendar Lookup'[Date]), DAY) <= 0
)
VAR OnTrack =
CALCULATE(
[Total items],
DATEDIFF('Data Table'[Due Date], MAX('Calendar Lookup'[Date]), DAY) <= -7
)
RETURN
SWITCH(
TRUE(),
MAX('Grouping Lookup'[Order]) = 3,
Overdue,
MAX('Grouping Lookup'[Order]) = 2,
DueSoon,
OnTrack
)
This seems to work ok, but the downside is, selecting any of the statuses in that visualisation, doesn't filter the other page visualisations.
Is there a way to rework how I've done this so that the filters apply across visualisations on the page?
thanks!
selecting any of the statuses in that visualisation, doesn't filter the other page visualisations
That is correct, you cannot impact the data model with a measure. However, you can use measures as visual filters, and you can create a disconnected table with all the possible values for that measure. This combination will then allow you to "show everythign that is on track" etc.