CountIf on multiple DAX Measure

[jdorby]

Hi Everyone,

I'm not even sure if this is possible, but I'm going to see if anyone can help. The below table is in PowerPivot. I'm trying to find out how many Projects/Opportunities sold services across multiple business practices. The company has multiple business lines/practices and one opportunity/project can be composed of several business lines or practices.

In the example attached, Prac1, Prac2, Prac3, etc. are MEASURES -- I'd like to essentially do a countif and say if there are dollar values in more than one practice that means that opportunity had cross-practice sales and mark it as "1", otherwise mark it as "0". Is there a way to do something like a countif, but against a Measure?

 

[Matt Allington]

It can certainly be done, but first I’m more interested in the 6 measures. What is that logic inside those measures?

I haven’t test it, but I think you could do either
Sumx(values(table[opportunity]),If(([measure 1]>0 + [measure 2]>0 + … etc)>1,1,0))
Or
Sumx(values(table[opportunity]),If((value([measure 1]>0)+ value( [measure 2]>0) + … etc)>1,1,0))

Make sure you get VALUES and VALUE exactly as written

Depending on the logic in the measures and the semantic model table structure, there may be a more efficient solution.

 

[jdorby]

Thank you so much for the response, Matt! I'm getting this error for both formulae

 

[jdorby]

Thank you so much for the response, Matt! I'm getting this error for both formulae

And my measures aren't anything special, just summing a column called Prac1:

=sum(Append1[Prac1])

 

[Matt Allington]

Sorry, my bad. I think somehow that DAX has changed over the years; I could be wrong. I'm thinking maybe an if statement that returns true if one or more measures are true. I don't have your data to test it, but try this.

=If([measure 1]>0 || [measure 2]>0 || [measure 3]>0 || [measure 4]>0 )>1,1,0))

I'm just not sure what will happen if there are 2 that are true.

Also, you should look at your data shape. if your formulas are
=sum(Append1[Prac1])
=sum(Append1[Prac2])
etc

Then your data is not optimal shape. Read about that here https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/