topic Re: DAX flag and dynamic interval measure in DAX Commands and Tips

DAX flag and dynamic interval measure

TSGD2123 — Thu, 12 Jan 2023 15:18:50 GMT

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

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

Thanks a lot in advance

Re: DAX flag and dynamic interval measure

johnt75 — Thu, 12 Jan 2023 16:47:24 GMT

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

Re: DAX flag and dynamic interval measure

TSGD2123 — Fri, 13 Jan 2023 08:20:08 GMT

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

Re: DAX flag and dynamic interval measure

johnt75 — Fri, 13 Jan 2023 09:40:16 GMT

But B was also inactive on 12th, that's why that is being returned also.

Re: DAX flag and dynamic interval measure

FreemanZ — Fri, 13 Jan 2023 09:52:39 GMT

hi johnt75

Could you explain how to use this measure?

Re: DAX flag and dynamic interval measure

johnt75 — Fri, 13 Jan 2023 09:54:46 GMT

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

Re: DAX flag and dynamic interval measure

TSGD2123 — Fri, 13 Jan 2023 10:06:00 GMT

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.

Re: DAX flag and dynamic interval measure

johnt75 — Fri, 13 Jan 2023 10:11:57 GMT

All 3 enterprises have an inactive entry for 12th, that's why they all show up whenever you select a day

Re: DAX flag and dynamic interval measure

TSGD2123 — Fri, 13 Jan 2023 10:45:01 GMT

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

Re: DAX flag and dynamic interval measure

johnt75 — Fri, 13 Jan 2023 10:52:31 GMT

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 )

Re: DAX flag and dynamic interval measure

TSGD2123 — Fri, 13 Jan 2023 12:06:03 GMT

Works wonderfully, thanks a lot for your time and patience johnt75

Re: DAX flag and dynamic interval measure

TSGD2123 — Fri, 13 Jan 2023 13:19:01 GMT

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!!

Re: DAX flag and dynamic interval measure

johnt75 — Fri, 13 Jan 2023 14:09:32 GMT

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 ) )