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

An alternative way without inactivating the relationship could be to adjust the code and use the ALLSELECTED to remove the effect of the relationship with the dates table.

 

This should work in similar way:

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

 

Hard to say what is good practice and not in this case since it depends very much on the rest of your model.
But please try different ways and see what seems to be best suitable for you!

 

Br,

Magnus

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous Leaving the relashionship as active and using ALLSELECTED doesn't work, it filters the final result back down based on the relationship between awards[Award Start Date] and dates[Cal Date]. 

 

I will read on the subject and surely there must be a way for DAX to handle 'Events-In-Progress' without deactivating the relationship between a fact table and the date table; otherwise most of the relationship to the date table will/should stay deactivated for such calculation to work. 

  • jsh121988's avatar
    jsh121988
    Microsoft Employee
    8 years ago

    I'm not sure if this will help, but it sounded like an issue i just had where I needed to deactivate a particular relationship.

    https://community.powerbi.com/t5/Desktop/Deactivate-Relationship-in-a-measure/m-p/454882#M210708

    Apparently, you wrap your calculate in another calculate with CROSSFILTER(col1,col2,None).

     

    _dmRollingAboveHours =
CALCULATE (
    CALCULATE ( // Existing Calculate
        SUMX ( ),
        FILTER ( )
    ),
    CROSSFILTER ( AH[NextChangedDate], _dtDateFilter[Date filter], None ) // Relationship you want to deactivate
)

    Thanks,

    Jon