Forum Discussion

pg9's avatar
pg9
Frequent Visitor
3 years ago
Solved

Counting column values for a matrix visual

Hi All, For a matrix visual, I'm looking to calculate the number of rows for a particular column.   Can someone suggest a dax measure for this please?
Need Help
Tips and Tricks
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi pg9 ,

     

    Please try:

    Measure = CALCULATE(COUNT(Sheet1[EmpIID]),FILTER(ALL(Sheet1),[Attribute] in ALLSELECTED('Sheet1 (2)'[Attribute]) &&[Value] in ALLSELECTED('Sheet1 (2)'[Value])))

    Final output:

    Best Regards,

    Jianbo Li

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

v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

Hi pg9 ,

 

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards,

Jianbo Li

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

pg9's avatar
pg9
Frequent Visitor
3 years ago

Hi v-jianboli-msft 

Please find the step by step below

Step 1 - I have pivoted the table and used matrix visual to view it. Below is how the table looks initially. I have used count of emplid to count the data. 

 

Step 2 - As I start choosing the selections from the field selections, the count keeps increasing. Please find the reference below

 

 

 

Requirement - I want to calculte the number of records in the emplID column alone.

 

Can you suggest how this can be achieved?

 

Please let me know if you need further input on this

Note - I have duplicate emplID records. So I'm not allowed to use distinct count.

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi pg9 ,

     

    By calculating it separately do you mean making its value independent of slicer and calculating the value in the table directly? If so, you can use the ALL function, like this:

    Measure = COUNTX(All('Table'[EmpID]'),[EmpID])

     

    Refer to:

    ALL function (DAX) - DAX | Microsoft Learn

     

    Best Regards,

    Jianbo Li

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

    • pg9's avatar
      pg9
      Frequent Visitor
      3 years ago

      I tried that DAX and it does not filter when I apply filter conditions. Please find the image below

      The requirement is for the Total count to change as and when we apply filter conditions. Can you help me with this please?

      • v-jianboli-msft's avatar
        v-jianboli-msft
        Community Support
        3 years ago

        Hi pg9 ,

         

        Apologies for my misunderstood before. Are you trying to calculate the "Total number of EmpID" while disregarding the impact of the "Field Selection" slicer, but allowing it to be influenced by the "Criteria Selection" slicer?

        If so, there are some information I need to check:

        If the slicer: "Field Selection" and "Category" are using the same data?

         

        Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

         

        Refer to:

        How to provide sample data in the Power BI Forum

        How to Get Your Question Answered Quickly

         

        Best Regards,

        Jianbo Li

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