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

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.