Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Using ALLEXCEPT or ALL not working

Hello,   Need some help with DAX please.   I need to count [Number of Active Awards] within a financial period. Then slice this measure by any dimension in the model, not only dates. Here's part ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    The pattern you use for solving the "events in progress" requires that you don't have any active relationship with the date table.

    If you inactivate the second relationship between Awards and Dates and then use this measure it will give you the active awards within the min/max dates.

     

    Total Active Awards = 
VAR MaxPeriod = MAX( dates[Cal Date] )
VAR MinPeriod = MIN( dates[Cal Date] )
RETURN
CALCULATE( COUNTROWS( awards ),
	  FILTER (
		awards,		
                    awards[Award Start Date] <= MaxPeriod
		&& awards[Award End Date] >= MinPeriod
	)
)

    There are several good articles written on the subject of "events in progress".

     

    Br,

    Magnus 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

The pattern you use for solving the "events in progress" requires that you don't have any active relationship with the date table.

If you inactivate the second relationship between Awards and Dates and then use this measure it will give you the active awards within the min/max dates.

 

Total Active Awards = 
VAR MaxPeriod = MAX( dates[Cal Date] )
VAR MinPeriod = MIN( dates[Cal Date] )
RETURN
CALCULATE( COUNTROWS( awards ),
	  FILTER (
		awards,		
                awards[Award Start Date] <= MaxPeriod
		&& awards[Award End Date] >= MinPeriod
	)
)

There are several good articles written on the subject of "events in progress".

 

Br,

Magnus 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous,

 

Brilliant that works; however, is it good practice to do this in the model because this will break other measures that are using this active relationship? Is there not a DAX function I can use to diactivate an active relationship just within that one measure (as in an opposite of USERELATIONSHIP)