Measure to count rows group by column

[Anonymous]

Hello all,

I have the following table:

Id	DimVoiceOperator_id
1	25
2	5
3	5
4	5
5	5
6	5
7	5
8	5
9	25
10	25
11	25
12	25
13	25
14	25

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

[Anonymous · Accepted solution]

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]

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

[Jihwan_Kim]

Count Operators : =

COUNTROWS(RELATEDTABLE(Data))

https://www.dropbox.com/s/odvfjxahmch5vtq/rmorais.pbix?dl=0 

[Anonymous]

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

[jdbuchanan71]

@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]

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]

@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