Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count non-blanks in Matrix

Hi all!

 

I've got this matrix based on Employees. Each employee can be in charge of multiple businesses, and each business has it's own Risk-level. 

 

Notice that Person B has a blank cell. I want the Risk-column to specificy how many non-blanks each Person has.

When the matrix has been collapsed on Employee, it should read:

Person A | 2

Person B | 1

Person C | 2

 

Is this possible?

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

     

    You can use the below Dax Expression.

    Risk m = 
IF( ISFILTERED(YourTable[Business]), MAX(YourTable[Risk]),  DISTINCTCOUNTNOBLANK(YourTable[Risk]))

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

     

    Can you provide a data sample?

    Many Thanks

    Mariusz

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Employee, Business, Risk

      Person A, Business X, High

      Person A, Business Y, Low

      Person B, Business W,,

      Person B, Business O, Medium

       

       

      Comma error, fixed.

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi Anonymous 

         

        You can use the below Dax Expression.

        Risk m = 
IF( ISFILTERED(YourTable[Business]), MAX(YourTable[Risk]),  DISTINCTCOUNTNOBLANK(YourTable[Risk]))

        Regards,
        Mariusz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.