Alright, this is a fun question and a big thank you for adding some dummy data so we can try stuff out in Power BI. The first thing we need to do is to create new relationships. The reason for this is that your dimension table (request_types) has multiple columns that can be linked to, that needs to be one. In you sample we could just choose the unique column (e.g. category) but this might not be a unique column in your real data set. To create a unique column in Request_types, let's add an index table to it in the power query editor:
Now every row is numbered uniquely. We just have to add calculated columns to the two other tables. The DAX for this is the following for table Event_clicks
CategoryIndex = LOOKUPVALUE(Request_Types[Index], Request_Types[Category], Event_clicks[Category], Request_Types[Type], Event_clicks[Product])
And for Service_requests:
CategoryIndex = LOOKUPVALUE(Request_Types[Index], Request_Types[Category], Service_requests[Request_category], Request_Types[Type], Service_requests[Request_type])
Now we are going to create relationships as follow:
These are one to many, and now you have a 'normal' datamodel. Now we are going to create a Table visual with the following columns:
Note that the first and second column are from the Request_types table, the third column is from Event_Clicks, the third is a distinct count of Reference column in Service_Request and the measure in the last column has the following DAX:
% of Service Requests = DIVIDE(DISTINCTCOUNT(Service_requests[Reference]), CALCULATE(DISTINCTCOUNT(Service_requests[Reference]), ALLSELECTED(Service_requests)))
My PBIX can be found here for your convience.
Let me know if this answers your question, and if you have any others please let me know 🙂 Kudo's are appreciated.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Alright, this is a fun question and a big thank you for adding some dummy data so we can try stuff out in Power BI. The first thing we need to do is to create new relationships. The reason for this is that your dimension table (request_types) has multiple columns that can be linked to, that needs to be one. In you sample we could just choose the unique column (e.g. category) but this might not be a unique column in your real data set. To create a unique column in Request_types, let's add an index table to it in the power query editor:
Now every row is numbered uniquely. We just have to add calculated columns to the two other tables. The DAX for this is the following for table Event_clicks
CategoryIndex = LOOKUPVALUE(Request_Types[Index], Request_Types[Category], Event_clicks[Category], Request_Types[Type], Event_clicks[Product])
And for Service_requests:
CategoryIndex = LOOKUPVALUE(Request_Types[Index], Request_Types[Category], Service_requests[Request_category], Request_Types[Type], Service_requests[Request_type])
Now we are going to create relationships as follow:
These are one to many, and now you have a 'normal' datamodel. Now we are going to create a Table visual with the following columns:
Note that the first and second column are from the Request_types table, the third column is from Event_Clicks, the third is a distinct count of Reference column in Service_Request and the measure in the last column has the following DAX:
% of Service Requests = DIVIDE(DISTINCTCOUNT(Service_requests[Reference]), CALCULATE(DISTINCTCOUNT(Service_requests[Reference]), ALLSELECTED(Service_requests)))
My PBIX can be found here for your convience.
Let me know if this answers your question, and if you have any others please let me know 🙂 Kudo's are appreciated.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Hi @JarroVGIT,
Thanks for your swift response.
Your suggested solution does fit my requirements. However, in my case, I'm unable to get a distinct count of Reference column even after replicating the suggested solution - quite puzzling given that Event_count does a sum as expected so would not expect that the Count (distinct) is 'broken'. This also affects the measure in the last column, as seen in the screenshot below:
Am I missing something?
I had a play around with the pbix - the reason that the count of reference was not showing is due to dynamic filtering based on slicer values from the calendar table.
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
