Power BI: Calculate Opposite Row Count from Measures

user_12208182 0
Viewed 103

I have a dashboard with a table that shows row counts for a database table according to their codes.

I have divided these counts between measures using CALCULATE to determine whether a row in the table contains a specific code. For example,

measure1 = CALCULATE(COUNT(table[id]),table[code]=1)

I made 5 measures with specific codes so that they are shown in the table as columns. I also use ANDs and ORs in the measures when comparing two codes, if more complex logic is needed.

Now, I have an idea for a new measure. I want to count all of the rows except the rows from the 5 previously created measures. How can I use those 5 measures in the logic of my new measure? Or, is there any other way to solve my problem?

dax powerbi
1 Answers

You wouldn't be able to reference your 5 previous measures directly in your new measure, since those measures result in a number (count), but don't give you any information about the logic behind how that number is calculated.

To accomplish what you want, you basically just want to negate the logic from those 5 measures directly in your new measure. For example, if 2 of your previous measures look like the following.

Measure 1 = CALCULATE(COUNT(table[id]), table[code] = 1)

and

Measure 2 = CALCULATE(COUNT(table[id]), table[code] = 2)

Then your new measure, which calculates the number of rows NOT counted in the above measures, would look like the following.

Measure 3 = CALCULATE(COUNT(table[id]), NOT(table[code] = 1), NOT(table[code] = 2))

You would do this negation with the logic from all 5 of your previous measures, rather than just the 2 given in the example above.

user_178844140
Related