Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure to count rows group by column

Hello all,

 

I have the following table:

IdDimVoiceOperator_id
125
25
35
45
55
65
75
85
925
1025
1125
1225
1325
1425

 

I want to display in a StackedBarChart the number of rows per DimVoiceOperator_id.

In this example:

   25 -> Count = 7

   5 -> Count = 7

 

I want to use a measure beacuse I need use some filters.

I have tried some functions as count and summarize but I was not able to get to final result.

 

Has someone have an idea how to perform this measure?

 

Thanks in advance,

Rui

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    I have built a data sample:

    Then you could use the following formula to create a measure:

    Measure 2 = CALCULATE(COUNTROWS('Table2'),ALLEXCEPT(Table1,Table1[DimVoiceOperator_id]))

    The final output is shown below:

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

7 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    Anonymous 

    This measure will give you the count of rows in the table.

    Count = COUNTROWS ( YourTable )

     Then you pull the DimVoiceOperator_id field into your visual and make sure it is set to not summarize.  Then put the measure in the visual.   That should give you what you are looking for.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      jdbuchanan71 thanks for the quick reply.

       

      Well more and less to be honest. Forgot to mention that I want to put on the Axis of the visual the name of that DimVoiceOperator_id and that is in another table. So those id's (5 and 25) are FK to another table.

      And I think that is my problem perahps.

       

      I in the visual I put the column "Name" from DimVoiceOperator table and the the values of the visual put the count rows, for each operator it gaves always 14.

       

      Thanks for your time

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi jdbuchanan71 I just find out that the automatic relation between the tables was wrong when I have imported the database so this was correct. I'm sorry for my previous response. I'm new at PowerBi and did not checked the relationships between the tables.

       

      Best regards,

      Rui

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jihwan_Kim  I just find out that the automatic relation between the tables was wrong when I have imported the database so this was correct. I'm sorry for my previous response. I'm new at PowerBi and did not checked the relationships between the tables.

       

      Best regards,

      Rui

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    I have built a data sample:

    Then you could use the following formula to create a measure:

    Measure 2 = CALCULATE(COUNTROWS('Table2'),ALLEXCEPT(Table1,Table1[DimVoiceOperator_id]))

    The final output is shown below:

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello Anonymous this is one of the solutions indeed. 

      I just find out that the automatic relation between the tables was wrong when I have imported the database so this was correct. I'm sorry for my previous response. I'm new at PowerBi and did not checked the relationships between the tables.

       

      Anyway thanks for your solution. And thanks for your time.

       

      Best regards,

      Rui