Forum Discussion

psilos24's avatar
psilos24
Helper I
5 years ago
Solved

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

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).
 
 

 

 

Need Help

8 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    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's avatar
      psilos24
      Helper I
      5 years ago

      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's avatar
        ryan_mayu
        Super User
        5 years ago

        psilos24 

        please try this

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