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 

DAX0110's avatar
DAX0110
Resolver V
8 years ago

Hi Anonymous, give this a try:

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    DAX0110 thank you. Your code returned an error "A single value for column 'Award End Date' in table awards cannot be determined. This can happen..."

     

    I then added awards[Award End Date] in the ALL but this is returning less records (587) than I expected (1391).

     

    Total Active Awards = 
VAR MaxPeriod = MAX( dates[Cal Date] )
VAR MinPeriod = MIN( dates[Cal Date] )
RETURN
CALCULATE( COUNTROWS( awards ),
		FILTER (
		       ALL( awards[Award Start Date],awards[Award End Date] ),
		        awards[Award Start Date] <= MaxPeriod
		            && awards[Award End Date] >= MinPeriod
		           )
)
    • DAX0110's avatar
      DAX0110
      Resolver V
      8 years ago

      mmm... OK, my bad, maybe this would work:

       

      Total Active Awards = 
VAR MaxPeriod = MAX( dates[Cal Date] )
VAR MinPeriod = MIN( dates[Cal Date] )
RETURN
CALCULATE( COUNTROWS( awards ),
        ALL(awards[Award Start Date]),
              awards[Award Start Date] <= MaxPeriod, 
        awards[Award End Date] >= MinPeriod
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        DAX0110 this still doesn't work, returning lesser rows than expected.