Secondary suppression using DAX

[betsysimpkins]

Hello all!

I am having issues creating a single measure that will suppress all numbers < 10 and if there is only 1 number that is < 10, then I need to suppress the next smallest number regardless of the value. The table below shows all the values. But in the corresponding bar chart, Native Hawaiian has correctly been changed to 0 for suppression....but the next smallest number (15) for Native Alaskan is still showing as 15...where it should also be 0 due to secondary suppression requirements. After lengthy conversations with ChatGPT we've arrived at the DAX measure below...but as you can see in the bar chart...it simply does not find the next lowest number and assign it a value of 0. Can anybody identify where the issue might be in the DAX logic to do this? OR better yet, is there a simpler, more elegant way to suppress all numbers < 10, and if there is only one in a group that is < 10, then find the next smallest number and suppress it as welll?  Thanks for any help!

Enrollment Rate with Secondary Suppression =

VAR AllCounts = VALUES('UnivEnrollment'[StudentID])

VAR LowCounts = CALCULATETABLE(

                    SUMMARIZE(

                        'UnivEnrollment',

                        'UnivEnrollment'[RaceEthnicity],

                        "TotalCount",[Enrollment Headcount for Slicer]

                    ),

                    FILTER(AllCounts, [Enrollment Headcount for Slicer] < 10)

                )

VAR MinLowCount = MINX(LowCounts, [TotalCount])

VAR NextLowCount =

    IF(

        COUNTROWS(LowCounts) = 1,

        0,

        MINX(AllCounts, [Enrollment Headcount for Slicer])

    )

RETURN

IF(

    [Enrollment Headcount for Slicer] < 10,

    IF(

         NextLowCount=0,

            0,

            [Enrollment Headcount for Slicer]

    ),

    [Enrollment Headcount for Slicer]

)

[Anonymous]

Hi @betsysimpkins ,

Depending on the information you have provided, l created a sample data to help you with your problem, you can follow these steps below:

Sample data:

1.Create new measure.

Result = 
VAR _Number =
    SELECTEDVALUE ( 'Table'[NUMBER] )
VAR _Numberlessthan10 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[NUMBER] ),
        FILTER ( 'Table', 'Table'[NUMBER] < 10 )
    )
VAR _Rank =
    RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[NUMBER] ) ),, ASC )
RETURN
    IF ( _Numberlessthan10 = 1 || _Rank <= 2, 0, _Number )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[betsysimpkins]

Thanks again for all your help...I've added a link here to some sample data from my Enrollment table. My measure to calculate the headcount of studentID is more complicated than needed for this sample data because of other filters I have to account for not shown in this dataset. But essentially it's just a distinct count of the studentID. Sample Data Extract for PBI 

[betsysimpkins]

Thank you Ada! However, my problem is that in your example you have a table with a column for the number. My numbers are generated from a measure that does a headcount of studentID from the larger Enrollment table. So I think for your solution to work for me, I need to somehow create a table of the calculated values first right? I can't make SELECTEDVALUE work with a measure.