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: 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...
  • 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
	)
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

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi johnt75 

Could you explain how to use this measure?

  • 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

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

      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.

       

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

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