DAX syntax for conditional measure to format a matrix based on value of a column?

0
votes

I have a matrix in Power BI which shows counts by Category and by Country (please see table 1 below for example).

Table 1

I want to create a measure to 'suppress' any counts in that matrix (by replacing the count with "...") that are lower than 3 IF the value of another column in the same source table = 1. IF the value is not equal to 1, I want to suppress any counts in that same matrix that are lower than 10.

To clarify, countries 1 to 5 have either a value of 1 or 2 for the column on which the suppression depends, and within the table it would look like this:

Suppression categories

With this measure in the values field, this would produce a matrix that looks like this:

Suppressed table 1

I have been able to achieve the first part (suppressing counts less than 3) using the following DAX syntax to create the measure:

Less than 3 = IF([COUNT]<3),"...",[FORMCOUNT]

But I have struggled to incorporate the second condition. I imagined it would look something like this:

Less than 3 or 10 = IF('Table'[conditional column] = 1
                    ,(IF(AND([COUNT]>0,[COUNT]<3),"...",[COUNT]))
                    ,(IF(AND([COUNT]>0,[COUNT]<10),"...",[COUNT])))

But that doesn't work. I have tried various variations of this syntax using functions such as FILTER that allow you to specify the column and condition but no luck.

Any pointers would be greatly appreciated, thanks!

1
powerbidax

1 Answers

0
votes

This may not be the perfect answer but the below appears to do what is required:

Two measures are required:

Measure1 = SELECTEDVALUE('Dataset'[Suppression category],"NA")

Measure2 = IF('Dataset'[Measure1] = 1,IF([COUNT]<3,"...",[COUNT]),IF([COUNT]<10,"...",[COUNT]))

Unfortunately can't currently explain why or how the first measure works, other than that I think it serves to select/distinguish between suppression categories 1 and 2.

The second measure uses the first measure as a condition in a nested IF statement, such that if the suppression category = 1, suppress the count if it is less than 3, otherwise do not suppress the count. IF the suppression is not 1, then suppress the count if it is less than 10. Otherwise, do not suppress the count.