Switch command including filter

[koorosh]

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 the following options.
Option1- filter table visual based on dafediff >60.

Option2- filter table visual based on datediff>100.

Option3- filter table visual based on dafediff>900.

Like what the filter pane does in photo1 and 2.

I do not want to use the filter pane. I want to use my own created slicer.

[Anonymous]

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]

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 · Accepted solution]

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.

[koorosh]

Could you please check my last question?
https://community.fabric.microsoft.com/t5/Desktop/Joint-table/m-p/3323771#M1109821