Search code examples
powerbidaxpowerbi-desktopdata-modeling

Supress values <5 - how to correct my DAX measure

I am trying to suppress numbers <5 in my matrix visualisation and not able to work out the correct solution. I am trying to build a table with 3 fields - Year, Sex and Number. Sex has Male, Female and Persons as options. The matrix visualisation layout is Year in Rows, Sex in Columns and Number in Values.

When I populate the table, I want to suppress values <5 for Male and Female, i.e if the values for Male or Female is <5 then show "<5" else show the actual value. For Persons I want to show blank if values for either Male or Female are <5 else show the actual values. I have created a dimension table for Sex (Sex_SortOrder) as I got multiple tables to work with and this will help me in sorting the right order for the Sex variable.

I have built the following DAX measure but it is not working for Persons. What am I doing wrong?

Here is my DAX measure.

Num_Suppress = 
VAR MaleValue = CALCULATE(SUM(HSUM_Results[Number]), Sex_SortOrder[Sex] = "Male")
VAR FemaleValue = CALCULATE(SUM(HSUM_Results[Number]), Sex_SortOrder[Sex] = "Female")
VAR PersonsValue = CALCULATE(SUM(HSUM_Results[Number]), Sex_SortOrder[Sex] = "Persons")
VAR ShowMale = IF(MaleValue >= 5, MaleValue, "<5")
VAR ShowFemale = IF(FemaleValue >= 5, FemaleValue, "<5")
VAR ShowPersons = IF(ShowMale = "<5" || ShowFemale = "<5", "-", PersonsValue)
RETURN
    SWITCH(TRUE(),
        SELECTEDVALUE(Sex_SortOrder[Sex]) = "Male", ShowMale,
        SELECTEDVALUE(Sex_SortOrder[Sex]) = "Female", ShowFemale,
        SELECTEDVALUE(Sex_SortOrder[Sex]) = "Persons", ShowPersons,
        BLANK()
    )

Please see the screenshot below for detail. I am also adding a link to the sample Power BI file for your reference.

Sample Power BI file

I would also want to apply the same principle to the DSR column, i.e. if Number of records for Male or Female is <5 then show Blank in the corresponding DSR cell and if either Male or Female numbers are <5 then Persons DSR is Blank.

enter image description here

Solution

  • You mean like this? I'm a bit confused by your model - is a male or a female a person or is person a third sex?

    enter image description here

    Num_Suppress = 
VAR MaleValue = CALCULATE(SUM(HSUM_Results[Number]), Sex_SortOrder[Sex] = "Male")
VAR FemaleValue = CALCULATE(SUM(HSUM_Results[Number]), Sex_SortOrder[Sex] = "Female")
VAR PersonsValue = CALCULATE(SUM(HSUM_Results[Number]), Sex_SortOrder[Sex] = "Persons")
VAR ShowMale = IF(MaleValue >= 5, MaleValue, "<5")
VAR ShowFemale = IF(FemaleValue >= 5, FemaleValue, "<5")
VAR ShowPersons =   IF(CALCULATE(SUM(HSUM_Results[Number]), Sex_SortOrder[Sex] = "Male", REMOVEFILTERS(Sex_SortOrder))  <5 || CALCULATE(SUM(HSUM_Results[Number]), Sex_SortOrder[Sex] = "Female", REMOVEFILTERS(Sex_SortOrder))  <5, "-", PersonsValue)
RETURN
    SWITCH(TRUE(),
        SELECTEDVALUE(Sex_SortOrder[Sex]) = "Male", ShowMale,
        SELECTEDVALUE(Sex_SortOrder[Sex]) = "Female", ShowFemale,
        SELECTEDVALUE(Sex_SortOrder[Sex]) = "Persons", ShowPersons,
        BLANK()
    )