Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @Anonymous ,

 

I have built a data sample:

data sample.PNG

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:

count based on two tables.PNG

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.

View solution in original post

7 REPLIES 7
v-eqin-msft
Community Support
Community Support

Hi @Anonymous ,

 

I have built a data sample:

data sample.PNG

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:

count based on two tables.PNG

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
Not applicable

Hello @v-eqin-msft 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
Super User
Super User

 

Count Operators : =
COUNTROWS(RELATEDTABLE(Data))
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

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
Super User
Super User

@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
Not applicable

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
Not applicable

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.