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: Country Mark Minutes AA aa11 30 AA aa22 60 ...
@PowerBI Report. @dax
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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
2 years ago

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.