Measure Problem - Matrix,count if has at least value.

[psilos24]

Hello guys,

I would like to create a measure and I have tried different techniques but with no luck.

I want to create a measure to count the number of rows based on the following criteria:

• If Period1 has at least a value and period2 has at least value  to count 

OR

• If Period2 has at least a value and period3 has at least value  to count 

OR

• If Period1 has at least a value and period3 has at least value  to count

For the example shown below, the answer should be 5 (highlighted rows).

[ryan_mayu · Accepted solution]

@psilos24 

please see the attachment below. hope this is helpful

[ryan_mayu]

@psilos24 

is this what you want?

Measure = 
VAR _count=DISTINCTCOUNT('Table (2)'[Period])
return if(sumx(VALUES('Table (2)'[Period]),_count)>1,1, sumx(VALUES('Table (2)'[Period]),_count))

i think this will affect both column total and row total.

[psilos24]

@ryan_mayu Thanks for the response, but it seems that is not working right. It showed the number 1 instead of 5 when I put it on the card.

[ryan_mayu]

@psilos24 

please try this

Measure = 
VAR _count=distinctcount('Table'[period])
VAR _count2=if(_count>1,1,_count)
return sumx(VALUES('Table'[period]),_count2)

[psilos24]

@ryan_mayu Thanks again for the response, but the answer should be 5 instead of 3.The measure should count the codes (256,258,262,264,269).

Let me remind you that the measure idea is the following.

• If Period1 has at least a value and period2 has at least a value to count. 

OR

• If Period2 has at least a value and period3 has at least a value to count. 

OR

• If Period1 has at least a value and period3 has at least a value to count.

[ryan_mayu · Accepted solution]

@psilos24 

please see the attachment below. hope this is helpful

[psilos24]

@ryan_mayu Thanks a lot. It works great.😊

I have something additional if you have the kindness to help me. I also want to create a measure to count the rows that have for sure a value at period 3 and (a value in period 2 OR period1). For this case, the answer should be 3 . (rows 258,262,269)

[ryan_mayu · Accepted solution]

@psilos24 

please see the attachment below

[psilos24]

@ryan_mayu Thanks a lot !!!