Forum Discussion

koorosh's avatar
koorosh
Icon for Post Partisan rankPost Partisan
3 years ago
Solved

Switch command including filter

Hi, Please check the attached file (testdays.pbix). I need a command to add to the switch command in the measure 'Dynamicmeasure' so that the slicer can filter the visual table on page one based on t...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi koorosh ,

     

    Try this code.

    DynamicMeasure = 
VAR _SELECT =
    SELECTEDVALUE ( MeasureListTable[MeasureTitle] )
RETURN
    SWITCH (
        _SELECT,
        "More than 60",
            CALCULATE (
                SUM ( 'Table'[DifferenceDays] ),
                FILTER ( 'Table', 'Table'[DifferenceDays] > 60 )
            ),
        "More than 100",
            CALCULATE (
                SUM ( 'Table'[DifferenceDays] ),
                FILTER ( 'Table', 'Table'[DifferenceDays] > 100 )
            ),
        "More than 900",
            CALCULATE (
                SUM ( 'Table'[DifferenceDays] ),
                FILTER ( 'Table', 'Table'[DifferenceDays] > 900 )
            ),    CALCULATE(SUM('Table'[DifferenceDays]))   
    )

    Result:

     

    Best Regards,
    Rico Zhou

     

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi koorosh ,

 

I suggest you to try code as below to create a measure.

DynamicMeasure = 
VAR _SELECT =
    SELECTEDVALUE ( MeasureListTable[MeasureTitle] )
RETURN
    SWITCH (
        _SELECT,
        "More than 60",
            CALCULATE (
                SUM ( 'Table'[DifferenceDays] ),
                FILTER ( 'Table', 'Table'[DifferenceDays] > 60 )
            ),
        "More than 100",
            CALCULATE (
                SUM ( 'Table'[DifferenceDays] ),
                FILTER ( 'Table', 'Table'[DifferenceDays] > 100 )
            ),
        "More than 900",
            CALCULATE (
                SUM ( 'Table'[DifferenceDays] ),
                FILTER ( 'Table', 'Table'[DifferenceDays] > 900 )
            )
    )

Result is as below.

 

Best Regards,
Rico Zhou

 

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

 

koorosh's avatar
koorosh
Icon for Post Partisan rankPost Partisan
3 years ago

Thanks. But we want to have all data in table visual when no option is selected. In this way, the visual would be empty.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi koorosh ,

     

    Try this code.

    DynamicMeasure = 
VAR _SELECT =
    SELECTEDVALUE ( MeasureListTable[MeasureTitle] )
RETURN
    SWITCH (
        _SELECT,
        "More than 60",
            CALCULATE (
                SUM ( 'Table'[DifferenceDays] ),
                FILTER ( 'Table', 'Table'[DifferenceDays] > 60 )
            ),
        "More than 100",
            CALCULATE (
                SUM ( 'Table'[DifferenceDays] ),
                FILTER ( 'Table', 'Table'[DifferenceDays] > 100 )
            ),
        "More than 900",
            CALCULATE (
                SUM ( 'Table'[DifferenceDays] ),
                FILTER ( 'Table', 'Table'[DifferenceDays] > 900 )
            ),    CALCULATE(SUM('Table'[DifferenceDays]))   
    )

    Result:

     

    Best Regards,
    Rico Zhou

     

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