Forum Discussion

rswami_4's avatar
rswami_4
Icon for Helper I rankHelper I
10 years ago
Solved

Change Filter Sequence in Power BI

I have a table that looks like this:

 

MyTable  
MyColumn1MyColumn2MyColumn3
10-AugValue 1X
10-AugValue 2 
10-AugValue 1X
11-AugValue 1 
11-AugValue 2X
12-AugValue 1X

 

I have a slicer on MyColumn2 (contains two values - Value 1 and Value 2)

 

I have a calculated column that looks like:

 

MyColumn 4 = COUNTROWS(
FILTER('MyTable' ,
 'MyTable'[MyColumn3] = "X" && 'MyTable'[MyColumn1] <= earlier('MyTable'[MyColumn1])
)
)

 

I need the slicer filter (on MyColumn2) to apply before the DAX Filter (MyColumn3) applies.  Otherwise the results are very different.  How can I make it happen?

  • rswami_4's avatar
    rswami_4
    10 years ago

    Eric,

     

    I figured out this issue though the solution was surprising to me.  First of all I was trying to create and use a calculated column like this instead (which is a variation of the previous one):

     

    MyColumn 4 = CALCULATE(COUNTROWS(
    FILTER(ALLEXCEPT('MyTable' , 'MyTable'[MyColumn2]
     'MyTable'[MyColumn3] = "X" && 'MyTable'[MyColumn1] <= earlier('MyTable'[MyColumn1])
    )
    )

     

    That did not work.  However, it worked when I created and used a calculate measure instead like this:

     

    MyMeasure 4 = CALCULATE(COUNTROWS(
    FILTER(ALLEXCEPT('MyTable' , 'MyTable'[MyColumn2]
     'MyTable'[MyColumn3] = "X" && 'MyTable'[MyColumn1] <= MAX('MyTable'[MyColumn1])
    )
    )

     

    Still cannot understand why a measure seems to have the filters applied in a different sequence from a column.  Basically, I was trying to use a slicer to display filtered cummulate values in a bar chart.

     

    Any idea when other forms of slicers will be available in Power BI ( like drop down menu, multiple selection, radio button, etc)?  Will really help make more versatile slicing and dicing of visualizations

     

     

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    rswami_4

     

    I guess that you're looking for something like

     

    MyColumn 4 =
COUNTROWS (
    FILTER (
        'MyTable',
        'MyTable'[MyColumn3] = "X"
            && 'MyTable'[MyColumn1] <= EARLIER ( 'MyTable'[MyColumn1] )
            && MyTable[MyColumn2] = EARLIER ( 'MyTable'[MyColumn2] )
    )
)

     

     

    If it is not the case, please be more specific on the expected output.

    • rswami_4's avatar
      rswami_4
      Icon for Helper I rankHelper I
      10 years ago

      Eric,

       

      I figured out this issue though the solution was surprising to me.  First of all I was trying to create and use a calculated column like this instead (which is a variation of the previous one):

       

      MyColumn 4 = CALCULATE(COUNTROWS(
      FILTER(ALLEXCEPT('MyTable' , 'MyTable'[MyColumn2]
       'MyTable'[MyColumn3] = "X" && 'MyTable'[MyColumn1] <= earlier('MyTable'[MyColumn1])
      )
      )

       

      That did not work.  However, it worked when I created and used a calculate measure instead like this:

       

      MyMeasure 4 = CALCULATE(COUNTROWS(
      FILTER(ALLEXCEPT('MyTable' , 'MyTable'[MyColumn2]
       'MyTable'[MyColumn3] = "X" && 'MyTable'[MyColumn1] <= MAX('MyTable'[MyColumn1])
      )
      )

       

      Still cannot understand why a measure seems to have the filters applied in a different sequence from a column.  Basically, I was trying to use a slicer to display filtered cummulate values in a bar chart.

       

      Any idea when other forms of slicers will be available in Power BI ( like drop down menu, multiple selection, radio button, etc)?  Will really help make more versatile slicing and dicing of visualizations