Forum Discussion

koorosh's avatar
koorosh
Post 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 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.

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.

4 Replies

  • 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
      Post 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.