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

To elaborate a little more, I have a card with the distinct count of TEAM. If I have 100 total teams in my data, and they all had some sort of activity in each of the 3 years, that card should remain 100 regardless of what combination of years I select. At any point, when I select "PASS" or "FAIL", those 2 values should add up to 100. They are when I slice by a single year right now. When I select multiple years, some teams are being duplicated. Specifically if they failed in 1 year and passed in another. I just need the pass/fail to evaluate the combined data, regardless of the year selection.