Forum Discussion

exe_binary's avatar
exe_binary
Frequent Visitor
2 years ago
Solved

Cumulative Filter for previous values

Hello folks,

I am newbiew in Power BI, and now I am struggiling to create a formula for filtering.
This would be my data set example:

CountryMarkMinutes
AAaa1130
AAaa2260
BBbb88120
BBbb00180
CCcc99380
DDdd55480
DDdd45550
EEee981440
EEee331520


Now, I would need to create a filter like this:
IF Minutes < 120, "< 2 hrs",
if minutes < 240, "< 4 hrs",
if minutes < 480, "< 8 hrs",
if minutes < 960, "< 16 hrs",
if minutes < 1440, "< 1 day",
if minutes >= 1440, "> 1 day"

But, if I select filter "< 8 hrs" I would like to include all values for "< 8 hrs", "< 4 hrs" and "< 2 hrs". 
Also, if I filter e.g. "< 4 hrs", I would need to include values for "< 4 hrs" and "< 2 hrs", and so on and so forth.

I tried several solutions, like first I created a calculated column:

 

 

NumericCategory = SWITCH(
    TRUE(),
    'Table1'[Minutes] < 120, 1,  // "< of 2 hrs"
    'Table1'[Minutes] < 240, 2,  // "< of 4 hrs"
    'Table1'[Minutes] < 480, 3,  // "< of 8 hrs"
    'Table1'[Minutes] < 960, 4,  // "< of 16 hrs"
    'Table1'[Minutes] < 1440, 5, // "< of 1 Day"
    'Table1'[Minutes] >= 1440, 6
)

 

 

  Then I created additinal calculated column:

 

 

TextCategory = 
SWITCH(
        [NumericCategory],
        1, "< 2 hrs",
        2, "< 4 hrs",
        3, "< 8 hrs",
        4, "< 16 hrs",
        5, "< 1 day",
        6, "> 1 day"
    )

 

 


And finally, I created a new measure:

 

 

CumulativeFilter = 
VAR SelectedTextCategory = SELECTEDVALUE(Table1[TextCategory])
VAR MaxNumericCategory = 
    SWITCH(
        SelectedTextCategory,
        "< 2 hrs", 1,
        "< 4 hrs", 2,
        "< 8 hrs", 3,
        "< 16 hrs", 4,
        "< 1 day", 5,
        "> 1 day", 6
        
    )
RETURN
CALCULATE(
    SUM(Table1[Minutes]),
    FILTER(
        Table1,
        Table1[NumericCategory] <= MaxNumericCategory
    )
)

 

 

And when I use "TextCategory" as a slicer and select e.g. "< 4 hrs" it filtered me only the records that are fitted for this condition. It won't me include the values for "< 2 hrs". 

Any idea how to achive this?

Thank you in advance.

  • exe_binary OK, one, I had an error in the measure:

     

    Selector = 
        VAR __Minutes = MAX('Table'[Minutes])
        VAR __Value = MAX('Filter table'[Minute Filter])
        VAR __MaxMinutes = 
        SWITCH( __Value,
            "< 2 hrs", 120,
            "< 4 hrs", 240,
            "< 8 hrs", 480,
            "< 16 hrs", 960,
            "< 1 day", 1440,
            1000000
        )
        VAR __Result = IF( __Minutes <= __MaxMinutes, 1, 0 )
    RETURN
      __Result

     

    Second, did you add the measure to the Filters pane and filter for 1? I attached a PBIX file that shows the correct configuration. Below signature.

5 Replies

  • exe_binary's avatar
    exe_binary
    Frequent Visitor

    Greg_Deckler - I fixed the error, so "Selector" works fine, but I don't know how to fix it to work for "> 1 day". 
    Finally, solution 🙂
    I needed to add additinal row with value "All" in disconnected table and to update a "Selector" like:

    Selector = 
            VAR __Minutes = MAX('Table1'[Minutes])
            VAR __Value = MAX('Filter table'[Minute Filter])
            VAR __MaxMinutes = 
            SWITCH( __Value,
                "< 2 hrs", 120,
                "< 4 hrs", 240,
                "< 8 hrs", 480,
                "< 16 hrs", 960,
                "< 1 day", 1440,
                "> 1 day", 1441,
                1000000
        
            )
            VAR __Result = IF(__MaxMinutes = 1441, IF(__Minutes>__MaxMinutes,1,0) ,IF(__Minutes<=__MaxMinutes,1,0))
        RETURN
        __Result

    Now, everything works fine.
    Thank you.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    exe_binary Use a disconnected table for your <2, <4, <8 values. You can then create a Complex Selector measure like this:

    Selector = 
      VAR __Minutes = MAX('Table'[Minutes])
      VAR __Value = MAX('Disconnected Table'[Value])
      VAR __MaxMinutes = 
        SWITCH( TRUE(),
          "<2", 120,
          "<4", 240,
          "<8", 480,
          "<16", 960,
          "<1 day", 1440,
          1000000
        )
      VAR __Result = IF( __Minutes <= __MaxMinutes, 1, 0 )
    RETURN
      __Result

     (3) The Complex Selector - Microsoft Fabric Community 

  • exe_binary's avatar
    exe_binary
    Frequent Visitor

    Greg_Deckler - sorry, maybe I did something wrong but it doesn't work for me.
    So, I created a new table with just one column (disconnected table):

    Then in my "Table1", I created a Selector per your instructions:

    Selector = 
    VAR __Minutes = MAX('Table1'[Minutes])
      VAR __Value = MAX('Filter table'[Minute Filter])
      VAR __MaxMinutes = 
        SWITCH( TRUE(),
          "< 2 hrs", 120,
          "< 4 hrs", 240,
          "< 8 hrs", 480,
          "< 16 hrs", 960,
          "< 1 day", 1440,
          1000000
        )
      VAR __Result = IF( __Minutes <= __MaxMinutes, 1, 0 )
    RETURN
      __Result

    Now, when I filter the table that I created from columns from Table1, nothing happens.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion

      exe_binary OK, one, I had an error in the measure:

       

      Selector = 
          VAR __Minutes = MAX('Table'[Minutes])
          VAR __Value = MAX('Filter table'[Minute Filter])
          VAR __MaxMinutes = 
          SWITCH( __Value,
              "< 2 hrs", 120,
              "< 4 hrs", 240,
              "< 8 hrs", 480,
              "< 16 hrs", 960,
              "< 1 day", 1440,
              1000000
          )
          VAR __Result = IF( __Minutes <= __MaxMinutes, 1, 0 )
      RETURN
        __Result

       

      Second, did you add the measure to the Filters pane and filter for 1? I attached a PBIX file that shows the correct configuration. Below signature.

      • exe_binary's avatar
        exe_binary
        Frequent Visitor

        Hey Greg_Deckler - I tested your PBI report and everything works fine except the value that are > 1440. Picture below. 
        I tried to modify the "Selector" but without success.

        Also, when I add "Selector" to my table, I get this error: