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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MSS
Frequent Visitor

Multiple IF statements in a measure added up per TRUE based on SELECTEDVALUES filtering

test = 
IF(
    SELECTEDVALUE(PowerBiData[Afdeling];"PROD")="PROD";1
)
+
IF(
    SELECTEDVALUE(PowerBiData[Afdeling];"MONT")="MONT";2
)
+
IF(
    SELECTEDVALUE(PowerBiData[Afdeling];"ADM")="ADM";3
)

Hi,

 

I am trying to create a measure "test", which is to sum up/add each TRUE in a single value. 
To elaborate, if statement 1 and 3 is found TRUE, then i would expect the value 4 (1+3=4).

I have a slicer, from where i select X different "Afdeling". So for each i select, my SELECTEDVALUE examines, whether each individual "Afdeling" is chosen, i.e. "PROD", "MONT", "ADM" and so on.

The thing is, the code works just fine if only one, none or all statements are true. But each time i choose between one and all, it will return the values as if in fact all statements were true.

 

Thanks in advance.

1 ACCEPTED SOLUTION

Hi @MSS 

You may try below measure:

TestCalc =
CALCULATE (
    SUMX (
        VALUES ( PowerBiData[Afdeling] ),
        SWITCH (
            TRUE (),
            PowerBiData[Afdeling] = "ADM", 3,
            PowerBiData[Afdeling] = "Mont", 2,
            PowerBiData[Afdeling] = "Prod", 1
        )
    )
)

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Gordonlilj
Solution Sage
Solution Sage

Hi,

Try this and see if it gives the result you are after

 

TestCalc = CALCULATE(SUMX( 
    Table;
    SWITCH (
        TRUE ();
        Table[Column] = "ADM"; 1;
        Table[Column] = "Mont"; 2;
        Table[Column] = "Prod";3
    )
))

Just change the names of Table and column to your own

Hi,

 

Thanks for your answer,

 

Unfortunately, this returns the number of rows, which have "Afdeling" = the types selected on the slicer.

 

The 1, 2 and 3 are not really the end result im reaching for. I want to have an unique expression to be executed per TRUE if-statement. That is;
"Afdeling" = "PROD", then do x thing,

"Afdeling" = "ADM", then do y thing,

"Afdeling" = "MONT", then do z thing.

 

And again, the type of "Afdeling" is to be chosen on a Slicer within the report.

Hi @MSS 

You may try below measure:

TestCalc =
CALCULATE (
    SUMX (
        VALUES ( PowerBiData[Afdeling] ),
        SWITCH (
            TRUE (),
            PowerBiData[Afdeling] = "ADM", 3,
            PowerBiData[Afdeling] = "Mont", 2,
            PowerBiData[Afdeling] = "Prod", 1
        )
    )
)

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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