Switch measure with multiple select slicer data showing up in the Matrix Visual

[jingxiawang]

Hi Support,

I'm creating a Power BI report to consolidate the G/L Account data from Business Central.

I'm using the Cronus demo database with 3 companies, CANADA, MEXICO and USA.

For example, if I own 100% of the Canada company, but only own 50% of the MEXICO and 50% of the USA company.

I would like when I multiple select the companies to consolidate from the slicer, it will calculate according to the consolidation percentage above and shows the data in the Matrix visual with the individual company listed.

My issue is that I want to create a measure with the SWITCH statement to calculate this. But I do not get the result I want to have the G/L account data listed under the companies I selected and take consideration into the consolidation percentage.

The attache screenshot is the report I created. I created the calculated meausure Country Amount to calculate the amount for different countries.

I also created below measures to calculate the amount for each country.

Total CAD = CALCULATE(SUMX('Consolidated Company', 'Consolidated Company'[Amount]),FILTER('Consolidated Company', 'Consolidated Company'[Currency Code]="CAD"))

Total MXN = CALCULATE(SUMX('Consolidated Company', 0.5*'Consolidated Company'[Amount]),FILTER('Consolidated Company', 'Consolidated Company'[Currency Code]="MXN"))

Total USD = CALCULATE(SUMX('Consolidated Company', 0.5*'Consolidated Company'[Amount]),FILTER('Consolidated Company', 'Consolidated Company'[Currency Code]="USD"))

Jessie.

[ibarrau]

Hi, before I write the mesure let me clarify that SWITCH will only comparte for only one value. If you click two items of the slicer it will go through "else" path. Consider that the first argument of the switch is the equal condition to check.

SWITCH ( SELECTEDVALUE ( 'Table'[Country] ) 
    ; "Mexico"; [Measure calculation for Mexico]
    ; "Canada"; [Measure calculation for Canada]
    ; "USA"; [Measure calculation for USA]
    ; [Else path calculation]
)

If you need to specificaly select two countries, then you can't use this swtich functions and you should build your own IF statements validating the combination of the selection.

IF ( 
    AND ( "Canada" IN VALUES('Table'[Country]) && "Mexico" IN VALUES('Table'[Country]) ; COUNTROWS ( VALUES ('Table'[Country]) ) = 2 )
    ; "True calculation"
    ; IF (
         AND ( ... //other combination
    )
    //final else    
)

That should work for multiple combinations.

Hope this helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jingxiawang · Accepted solution]

Hi ibarrau,

Thank you for the reply.

I made it work by using the Amount in the Matrix visual, then drag the Country Amount to the same Matrix to compare the consoliated amount with the original amount. Amount is the column summed from the original table.

Somehow the Switch statement works when I select the multiple countries in the slicer by doing it this way.

Jessie.