cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
magsod
Solution Supplier
Solution Supplier

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 

View solution in original post

17 REPLIES 17
Kbal
Regular Visitor

 
magsod
Solution Supplier
Solution Supplier

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
Not applicable

@magsod,

 

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)

magsod
Solution Supplier
Solution Supplier

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
Not applicable

@magsod 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. 

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

v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

Based on the above, you may use ALLSELECTED Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-chuncz-msft, thank you. This function doesn't work either, gives me lesser records than expected.

 

I need ALL records from table 'awards' where awards[Award Start Date] <= MaxPeriodDate && awards[Awards End Date] >= MinPeriodDate. Then I need to be able to slice this number by other dimensions, not only dates, as per the model I sent in my question.

Anonymous
Not applicable

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

 

Anonymous
Not applicable

@Anonymous 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. 

Anonymous
Not applicable

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

DAX0110
Resolver V
Resolver V

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
Not applicable

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

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
Not applicable

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

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

Anonymous
Not applicable

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

 

Is there anyone who can help me with this please? 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors