Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX - ALLEXCEPT, fields from related tables

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?

  • 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

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity 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