Forum Discussion

nickvogel's avatar
nickvogel
Frequent Visitor
5 years ago
Solved

Need A Pass/Fail Slicer Across Multiple Years

I'm going to explain in simplified terms, and hopefully someone will know how to help me. Let's say I have a data table with 3 columns: TEAM, YEAR, and PROFIT. I need to be able to evaluate PROFIT, i...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, nickvogel ;

    You could create a flag measure.

    flag =
IF (
    NOT ( ISFILTERED ( 'Table'[Year] ) ) && NOT ( ISFILTERED ( 'slicer'[slicer] ) ),
    1,
    IF (
        SELECTEDVALUE ( 'slicer'[slicer] ) = "Fail"
            && CALCULATE ( SUM ( [Profit] ), FILTER ( 'Table', [Team] = MAX ( [Team] ) ) ) < 0,
        1,
        IF (
            SELECTEDVALUE ( 'slicer'[slicer] ) = "Pass"
                && CALCULATE ( SUM ( [Profit] ), FILTER ( 'Table', [Team] = MAX ( [Team] ) ) ) > 0,
            1
        )
    )
)

    Then apply it into filter.

    In addition , create a count measure.

    count = DISTINCTCOUNT('Table'[Team])

    The final output is shown below:

       

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

nickvogel's avatar
nickvogel
Frequent Visitor
5 years ago

1. Here is the sample (very simplified) data.

2. Here is a very simplified view of my report, with no slicers selected. Just wide open.

Now, if I were to select 2020, and FAIL, the report should look like this.

3. I need to figure out a way for my Pass/Fail slicer to filter the report appropriately, as shown above. Team A and Team D had negative profits in 2020, meaning they would fail the evaluation. I need this to work, regarldess of how many years are selected. For example, in the wide-open report view above, if I would've selected "FAIL", Team C would've been the only team to remain in the table, and the Count of Team visual would've shown 2. Because that was the only team that failed in the combined 3 years. 

 

Hopefully this helps, thank you!





nickvogel's avatar
nickvogel
Frequent Visitor
5 years ago

would've shown 1***, sorry.