Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Axit
Advocate II
Advocate II

Secondary suppression in a matrix table

We have to deal a lot with data confidentiality. At the moment I can do primary suppression using this formula in DAX:

 

Patient Counts =
IF (
CALCULATE ( SUM ( Patient[counter] ) ) < 5 && SUM ( Patient[counter] ) > 0,
"< 5",
CALCULATE ( SUM ( Patient[counter] ) )
)

 

That works great if there are no totals or sub-totals in the table. However, we do want to include totals for our clients. The problem is if I've suppressed a value in the table of 4, the remaining value is 6 and the total is 10, somebody could quickly work out that the "< 5" value is 4. Therefore, I need to use what we call "secondary suppression" to suppress the 6.

 

Thank you for your time.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @Axit,

 

You can try to add condition on category column to ignore calculate on total level.

Sample measure:

Patient Counts =
IF (
    HASONEVALUE ( Patient[Category Column] ),
    IF (
        SUM ( Patient[counter] ) < 5
            && SUM ( Patient[counter] ) > 0,
        "< 5",
        SUM ( Patient[counter] )
    )
)

Notice: Replace bold part with your category column name.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @Axit,

 

You can try to add condition on category column to ignore calculate on total level.

Sample measure:

Patient Counts =
IF (
    HASONEVALUE ( Patient[Category Column] ),
    IF (
        SUM ( Patient[counter] ) < 5
            && SUM ( Patient[counter] ) > 0,
        "< 5",
        SUM ( Patient[counter] )
    )
)

Notice: Replace bold part with your category column name.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks @v-shex-msft this works and stops the calculation for total. Is it at all possible to keep totals where there is no suppression in a row?

Hi @Axit,

 

So you means you want to prevent subtotal level and keep total level displayed?

Maybe you can try to use below measure:

Patient Counts =
IF (
    HASONEVALUE ( Patient[Category Column] )
        || COUNTROWS ( Patient ) = COUNTROWS ( ALLSELECTED ( Patient ) ),
    IF (
        SUM ( Patient[counter] ) < 5
            && SUM ( Patient[counter] ) > 0,
        "< 5",
        SUM ( Patient[counter] )
    )
)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.