Wrong Total for SWITCH TRUE

[Pikachu-Power]

Hi all,

i get a wrong total (or no total) for the following measure:

Measure_Column =

SWITCH(TRUE(),

SELECTEDVALUE(Table[Position]) = "PositionName1", [Measure1],

SELECTEDVALUE(Table[Position]) = "PositionName2", [Measure1],

SELECTEDVALUE(Table[Position]) = "PositionName3", [Mesaure2],

SELECTEDVALUE(Table[Position]) = "PositionName4", [Mesaure2],

SELECTEDVALUE(Table[Position]) = "PositionName5", [Mesaure2],

"ELSE CONDITION ???"

)

The SWITCH function has an argument for an ELSE condition. Is it possible to use that in this case? Or what would be the best way?

There is a possibiliy with IF and HASONEVALUE but that looks very ugly. 

Thanks!

[v-yangliu-msft]

Hi  @Pikachu-Power  ,

Not very clear what you mean, you mean the filter inside it calcuated.

It can not only restrict two conditions, but can have many restrictions. Use && as an intermediate connecting character, for example:

Measure =
CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[flag]="PositionName1"&&'Table'[ID]>=1&&'Table'[ID]<=10))

If you mean the switch inside the condition, whether written or write directly measure the original formula is feasible

When a certain condition is met, the corresponding result will be obtained, if the condition is not met, the final result will be directly obtained.

Measure_switch =
SWITCH(
    TRUE(),
SELECTEDVALUE('Table'[flag])="PositionName1",CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[flag]="PositionName1"&&'Table'[ID]>=1&&'Table'[ID]<=10)), SELECTEDVALUE('Table'[flag])="PositionName2",CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[flag]="PositionName1"&&'Table'[ID]>=1&&'Table'[ID]<=10)),
SELECTEDVALUE('Table'[flag])="PositionName3",CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[flag]="PositionName3"&&'Table'[ID]>=5&&'Table'[ID]<=11)),
'Table'[Measure 1]+'Table'[Measure 2]+'Table'[Measure 3])

If it does not meet your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

Best Regards,

Liu Yang

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

[v-yangliu-msft]

Hi  @Pikachu-Power  ,

Switch can use the Else condition at the end. When the above conditions are not met, the result will be obtained directly.

I created some data:

1. Create meausre.

Measure1 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=3))

Measure 2 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=4))

Measure 3 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=5))

Measure 4 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=6))

Measure 5 = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ID]<=7))

Measure_switch =
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[flag])="PositionName1",'Table'[Measure 1],
    SELECTEDVALUE('Table'[flag])="PositionName2",'Table'[Measure 2],
    SELECTEDVALUE('Table'[flag])="PositionName3",'Table'[Measure 3],
    SELECTEDVALUE('Table'[flag])="PositionName4",'Table'[Measure 4],
    SELECTEDVALUE('Table'[flag])="PositionName5",'Table'[Measure 5],
    'Table'[Measure 1]+'Table'[Measure 2]+'Table'[Measure 3]+'Table'[Measure 4]+'Table'[Measure 5])

2. Result:

When you select PositionName6 that does not meet any of the above conditions, all of its additions are displayed

Best Regards,

Liu Yang

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

[Pikachu-Power]

Thanks but looks complicated for my case 🙂 My measures have three or more restrictions and FILTER accept only two.

And what if

SWITCH(
TRUE(),
SELECTEDVALUE('Table'[flag])="PositionName1",'Table'[Measure 1],
SELECTEDVALUE('Table'[flag])="PositionName2",'Table'[Measure 1],
SELECTEDVALUE('Table'[flag])="PositionName3",'Table'[Measure 3],

...

[amitchandak]

@Pikachu-Power , Try like

Measure_Column =
sumx(values(Table[Position]), SWITCH(TRUE(),
SELECTEDVALUE(Table[Position]) = "PositionName1", [Measure1],
SELECTEDVALUE(Table[Position]) = "PositionName2", [Measure1],
SELECTEDVALUE(Table[Position]) = "PositionName3", [Mesaure2],
SELECTEDVALUE(Table[Position]) = "PositionName4", [Mesaure2],
SELECTEDVALUE(Table[Position]) = "PositionName5", [Mesaure2],
blank()
))

[Pikachu-Power]

Hello, seems not to work yet... I get BLANK for the Total.