Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have some data like:
Enterprise | Date | Flag active |
A | 09/01/2023 | 1 |
A | 10/01/2023 | 0 |
A | 11/01/2023 | 0 |
A | 12/01/2023 | 0 |
B | 10/01/2023 | 0 |
B | 11/01/2023 | 1 |
B | 12/01/2023 | 0 |
C | 10/01/2023 | 0 |
C | 11/01/2023 | 0 |
C | 12/01/2023 | 0 |
I'm asked to create a visual table displaying the inactive enterprises for the past x days:
Lets say today is 12/1/23 and for the past 3 days (10, 11, and 12) would be:
Enterprise |
A |
C |
The enterprises not active on days 11 and 10.
I need:
1) Create a measure or anything to show that output
2) Make the time period dynamic :
Enterprise |
A |
B |
C |
Thanks a lot in advance
Solved! Go to Solution.
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
)
You'd need to set up a numeric range parameter called Num days then you could create a measure like
Enterprise is visible =
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] )
)
RETURN
IF ( CurrentEnterprise IN InactiveEnterprises, 1 )
and this is a visual level filter to a table visual set to only show when the value is 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
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.
All 3 enterprises have an inactive entry for 12th, that's why they all show up whenever you select a day
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
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
)
One last question @johnt75 please, is it possible to create anothe measure to show the distinctcount of Enterprises not active for the current selected period? (3 when day 1; 2 when day 2...)
Thanks!!
VAR NumDays = SELECTEDVALUE( 'Num days'[Num days] )
VAR StartDate = TODAY( ) - NumDays
VAR ChosenDates = CALENDAR( StartDate, TODAY( ) )
VAR InactiveEnterprises =
CALCULATETABLE(
VALUES( 'Table'[Enterprise] ),
REMOVEFILTERS( 'Table'[Enterprise]),
TREATAS( ChosenDates, 'Table'[Date] ),
TREATAS( { 0 }, 'Table'[Flag active] )
)
VAR ActiveEnterprises =
CALCULATETABLE(
VALUES( 'Table'[Enterprise] ),
REMOVEFILTERS( 'Table'[Enterprise]),
TREATAS( ChosenDates, 'Table'[Date] ),
TREATAS( { 1 }, 'Table'[Flag active] )
)
RETURN
COUNTROWS(
EXCEPT( InactiveEnterprises, ActiveEnterprises )
)
Not working, here is the desired output in case I didn't explain myself 😉
Today (13-1-2023)
- 1 day: A,B,C (inactive on 12-1)
- 2 days: A,C (inactive on 11-1 and 12-1)
- 3 days: A,C (inactive on 10-1, 11-1 and 12-1)
Thanks
But B was also inactive on 12th, that's why that is being returned also.
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
86 | |
46 | |
28 | |
22 |