DAX - ALLEXCEPT, fields from related tables

[Anonymous]

I've created three tables. Structure on screen.

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:

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?

[v-frfei-msft · Accepted solution]

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