Forum Discussion
DAX flag and dynamic interval measure
- 3 years ago
Right, you want to exclude enterprises which have an active entry during the period. You can use
VAR CurrentEnterprise = SELECTEDVALUE( 'Table'[Enterprise] ) VAR NumDays = SELECTEDVALUE( 'Num days'[Num days] ) VAR StartDate = TODAY( ) - NumDays VAR ChosenDates = CALENDAR( StartDate, TODAY( ) ) VAR InactiveEnterprises = CALCULATETABLE( VALUES( 'Table'[Enterprise] ), TREATAS( ChosenDates, 'Table'[Date] ), TREATAS( { 0 }, 'Table'[Flag active] ) ) VAR ActiveEnterprises = CALCULATETABLE( VALUES( 'Table'[Enterprise] ), TREATAS( ChosenDates, 'Table'[Date] ), TREATAS( { 1 }, 'Table'[Flag active] ) ) RETURN IF( CurrentEnterprise IN EXCEPT( InactiveEnterprises, ActiveEnterprises ), 1 )
Put the Enterprise column into a table or matrix and then add the measure as a visual level filter, to only show when the value is 1
- TSGD21233 years ago
Helper I
Unfortunately I cannot enclose the pbix but here here the screenshots:
As can be seen, if I select any day, all 3 enterprises have measure value 1, if none are selected, no enterprise shows up.
- johnt753 years ago
Super User
All 3 enterprises have an inactive entry for 12th, that's why they all show up whenever you select a day
- TSGD21233 years ago
Helper I
The flag=1 is active:
- Day 1 selected: date: 12th: Enterprises A, B, C are inactive. Desired output: A, B, C
- Day 2 selected: dates: 11th, 12th: Enterprises A, C are inactive on both 11th or 12th. Desired output: A, C
- Day 3 selected: dates: 10th,11th, 12th: Enterprises A, C are inactive on 10th,11th and 12th, B has been active on the 3 day period so: Desired output: A, C
Basically you select the amount of days you go back from today, thats your period, I need the enterprises that have been inactive (flag=0) through the period to show up, if there's one single day a company has flag = 1, it doesn't have to appear.
Sorry I wasn`t clear previously