Forum Discussion

ravdha85's avatar
ravdha85
Frequent Visitor
3 years ago

Need Help with DAX measure

Hi Experts,

I have a use case in a self service report Model where end users can select one column at a time from two or more different tables in to a table visual, based on that my measure's row and total values needs to be correct.

Tables-Table1, Tabble2....

Measure Column-Sales[Amount]

 

=if(ISFILTERED('Table1'),SUM('Sales'[Amount]),
SUMX(VALUES('Table1'),CALCULATE(SUM('Sales'[Amount]))-- I am not able to use elseif condition here for other tables

 

In the above measure I have used ISFILTERED to dynamically check for any columns from Table1, but I am unable to use elseif condition to use Table2,Table3..etc with the column measure 'Sales'[Amount]. Please let me know a workaround/Solution for this issue. Rally appreciate your help on this

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ravdha85 ,

     

    You can use the SWITCH function in DAX to handle multiple conditions.

    Measure =
SWITCH (
    TRUE (),
    ISFILTERED ( 'Table1' ), SUM ( 'Sales'[Amount] ),
    ISFILTERED ( 'Table2' ), SUM ( 'Sales'[Amount] ),
    -- Add more conditions for other tables if needed
    SUM ( 'Sales'[Amount] ) -- Default value if none of the conditions are met
)

    Please refer to the following document for more information.

    SWITCH function (DAX) - DAX | Microsoft Learn

     

    Best Regards,

    Neeko Tang

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

  • ravdha85's avatar
    ravdha85
    Frequent Visitor
    3 years ago

    Hi Anonymous 
    I am not able to use the Switch function, for eg. I have 3 different meaures like below which I would like to have it in a switch or if condition

    Measure1=ISFILTERED('Table1'),SUM('Sales'[Amount]),

    SUMX(VALUES('Table1'),CALCULATE(SUM('Sales'[Amount])

    Measure2=    ISFILTERED('Table2'),SUM('Sales'[Amount]),
    SUMX(VALUES('Table2'),CALCULATE(SUM('Sales'[Amount])

    Measure3=    ISFILTERED('Table3'),SUM('Sales'[Amount]),
    SUMX(VALUES('Table3'),CALCULATE(SUM('Sales'[Amount])
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi ravdha85 ,

       

      Try it.

      CombinedMeasure =
IF (
    ISFILTERED ( 'Table1' ),
    SUMX ( VALUES ( 'Table1' ), CALCULATE ( SUM ( 'Sales'[Amount] ) ) ),
    IF (
        ISFILTERED ( 'Table2' ),
        SUMX ( VALUES ( 'Table2' ), CALCULATE ( SUM ( 'Sales'[Amount] ) ) ),
        IF (
            ISFILTERED ( 'Table3' ),
            SUMX ( VALUES ( 'Table3' ), CALCULATE ( SUM ( 'Sales'[Amount] ) ) ),
            SUM ( 'Sales'[Amount] ) -- Default value if none of the conditions are met
        )
    )
)

      Best Regards,

      Neeko Tang

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