Clustered column chart filtered measure

[Krause32]

Hello everyone

I am fairly new to Power BI, but I've been having this problem multiple times since I started now. 

The project I'm currently working on invovles a variable (unique personal ID's) and a measure that equals the total amount of calls (=285)

I need to create a clustered column chart that shows 5 bars, where each bar ranging from 1-5 has the total amount of unique personal ID's based on amount of calls. 
So, 
1. Group of unique personal ID's all have 1 amount of calls each
2. Group of unique personal ID's all have 2 amount of calls each

...

5. Group of unique personal ID's all have 5 amount of calls each or more

This below shows the expected graph or what I want it to look like (accidentally drew 4th and 5h bar too tall, sry)

I am able to make tables (on the dashboard) that gives me the correct answers, but ofc they are in a table and not a clustered column chart, see picture below.

Right now, I am only able to make this graph:

However, this graph shows all the ID's and the amount of calls each has. As I said, I want it group the ID's based on the amount of calls each ID has. 

I have been struggling with this for a week now and cannot seem to understand how to grasp this, and I hope some of you smart power BI minds know how to solve this problem.

Notes:
- I am not able to make new tables as my Power Bi report is connected to a live server, so this needs to be done with measures only. 

Best regards, 
Rasmus

[Anonymous · Accepted solution]

Hi  @Krause32 

You can refer to the following example.

Sample data 

Sample measure

#call = COUNTROWS('Table')

distinctcal = DISTINCTCOUNT('Table'[Column1])

Then create a new table

Table 2 = GENERATESERIES(1,10,1)

Then put the value of the table to the x-axis, and create a measure and put the measure to y-axis

Measure 3 = CALCULATE([distinctcal],FILTER('Table',[#call]=SELECTEDVALUE('Table 2'[Value])))

Output

Best Regards!

Yolo Zhu

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

[Anonymous · Accepted solution]

Hi  @Krause32 

You can refer to the following example.

Sample data 

Sample measure

#call = COUNTROWS('Table')

distinctcal = DISTINCTCOUNT('Table'[Column1])

Then create a new table

Table 2 = GENERATESERIES(1,10,1)

Then put the value of the table to the x-axis, and create a measure and put the measure to y-axis

Measure 3 = CALCULATE([distinctcal],FILTER('Table',[#call]=SELECTEDVALUE('Table 2'[Value])))

Output

Best Regards!

Yolo Zhu

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

[Krause32 · Accepted solution]

Hey Yolo Zhu

Thanks for your reply and take on this question. 

Unfortunately, I am not able to create tables as you showed in the first part of your reply. I do not have access to composite models as it is running on live database. The whole point is, I need to make the graph but ONLY with measures. 

Best regards,

Rasmus

[Anonymous]

Hi @Krause32 

But if you want to display related numbers on the x-axis, you must use a real column, measures cannot be placed on the x-axis, maybe you can consider adding a new table to the live database and then connecting it to PowerBI, as I said above.

Best Regards!

Yolo Zhu

[Krause32]

Hey Again 

Do you know if it possible to create a table or composite model while being on SQL Live server?

[Krause32]

Hey again,

Okay, I will see if I can do that. 

Thank you so much for clearing things out for me! 

Best regards,

Rasmus