Forum Discussion

TSGD2123's avatar
TSGD2123
Icon for Helper I rankHelper I
3 years ago
Solved

DAX flag and dynamic interval measure

Hi all,

 

I have some data like:

EnterpriseDateFlag active
A09/01/2023 1
A10/01/2023 0
A11/01/2023 0
A12/01/2023 0
B10/01/2023 0
B11/01/2023 1
B12/01/2023 0
C10/01/2023 0
C11/01/2023 0
C12/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

  • johnt75's avatar
    johnt75
    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
	)

12 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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

    • TSGD2123's avatar
      TSGD2123
      Icon for Helper I rankHelper I
      3 years ago

      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

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

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

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        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