Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX - ALLEXCEPT, fields from related tables

I've created three tables. Structure on screen.

 

02.PNG

 

Relationships:

DimPowerPlant(Id)(1) - (1)FactNuclearPowerPlant(Id)

DimCountryCode(Id)(1) - (*)FactNuclearPowerPlant(CountryNumericCode)

 

I've created Matrix visual. In rows, I put DimCountryCode(CountryName) and DimPowerPlant(ReactorType). As values I wanted to show occurences for every country in groups and subgroups. Like bellow - Total05 measure:


01.PNG

At first I had written this code.

Total02 = CALCULATE(COUNTROWS(FactNuclearPowerPlant);ALLEXCEPT(DimCountryCode;DimCountryCode[CountryName]))

But it doesnt't work properly. It works when all the data are in one table.

So, I've created this measure:

Total05 = CALCULATE(COUNTROWS(FactNuclearPowerPlant);FILTER(ALLEXCEPT(FactNuclearPowerPlant;DimCountryCode[CountryName]);COUNTROWS(FactNuclearPowerPlant) <> BLANK()))

It works well, but I can't understand how this piece of code works and why Total02 measure doesn't work correctly in this case. 

FILTER(ALLEXCEPT(FactNuclearPowerPlant;DimCountryCode[CountryName]);COUNTROWS(FactNuclearPowerPlant) <> BLANK())

Maybe there is a better way to do it?

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

As the measure total02, the column DimCountryCode[CountryName] is used to the visual, the results are different. Because DimCountryCode is specified as the argument to ALLEXCEPT, when the data is filtered, a filter will be applied on country at the row level, as shown in your table visual. 

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

1 REPLY 1
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

As the measure total02, the column DimCountryCode[CountryName] is used to the visual, the results are different. Because DimCountryCode is specified as the argument to ALLEXCEPT, when the data is filtered, a filter will be applied on country at the row level, as shown in your table visual. 

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.