cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
veejess Regular Visitor
Regular Visitor

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 of the model. Awards[Award Start Date] = Dates[Cal Date] (active relationship) and Awards[Award End Date] = Dates[Cal Date] (inactive relationship). 

 

pic3.JPG

 

 

THE PROBLEM

 

If I use ALL(awards), I get a correct count only if I slice/filter by a date but slice by any other column, I get the total in all rows.

 

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

 

pic1.JPG

 

 

If I use ALLEXCEPT(awards, awards[Award Division], projects[Project Status]), I get the correct result as below but then every new dimension I want to slice by I will have to add this column to the ALLEXCEPT. Surely I'm not using the correct DAX function here, please advise?

 

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

 

pic2.JPG

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
magsod Member
Member

Re: Using ALLEXCEPT or ALL not working

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 

17 REPLIES 17
DAX0110 Member
Member

Re: Using ALLEXCEPT or ALL not working

Hi @veejess, 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
	)
)

 

veejess Regular Visitor
Regular Visitor

Re: Using ALLEXCEPT or ALL not working

@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 Member
Member

Re: Using ALLEXCEPT or ALL not working

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 )
veejess Regular Visitor
Regular Visitor

Re: Using ALLEXCEPT or ALL not working

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

DAX0110 Member
Member

Re: Using ALLEXCEPT or ALL not working

If you upload the pbix file I'l have a look.

veejess Regular Visitor
Regular Visitor

Re: Using ALLEXCEPT or ALL not working

@DAX0110 the data model contains sensitive data so unable to upload here.

 

Is there anyone who can help me with this please? 

Re: Using ALLEXCEPT or ALL not working

Give this a try... If you use ALL in filter conditions it will just ignore the slicers and overwrites with all the rows.

 

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
	)
)

 

veejess Regular Visitor
Regular Visitor

Re: Using ALLEXCEPT or ALL not working

@nvpraveenyakkal thank you. Unfortunately, this is not producing expected results. I'm aware ALL will remove all the filters, the problem I'm having is as I explained above. 

veejess Regular Visitor
Regular Visitor

Re: Using ALLEXCEPT or ALL not working

@v-ljerr-msft any help will be much appreciated. Thanks