Showing results for 
Search instead for 
Did you mean: 
dbravo Frequent Visitor
Frequent Visitor

Many to many and two dimensions



I've got a problem with three dimensions and a M2M relationship between that tables.


Simplifyng the model, the data is this:




Case is the fact table, I got 3 dimensions, Service, Location and DeviceType, and there are 2 M2M relations, one between Services and Device and one between Location and Device (I could merge Service and DeviceTypeService, or Case with Service and DeviceTypeService but it does not solve the problem).


When a Case is opened the device is related with the case through the Service.




With a Matrix visual I want to know how many cases are opened by location and how many devices in total are in the location for the service of the case. For example, in the location 1 there are opened 2 cases and there are 3 devices in total for the services on the case. With this values I want to calculate a measure, Case Index = Cases Opened By DeviceType in Location / All Devices by DeviceType In Location.


With my current model I get this:




Please could you help me?

Thanks In Advance.

Super User
Super User

Re: Many to many and two dimensions



currently I'm working on something that has a similar structure. There are packages, that have more than one release (luckily), and also these packages can be assigned to more than "Taskview" (something that groups the package).


Now I was trying to answer the following question: How do the Taskviews develop over time and I came up with the following datamodel:


Many Many to many relationships.png


I combined the 2 many-to-many tables into one table, building the cartesian product of both tables and created a measure "No of Packages" that is just a simple "COUNTDISTINCT"


This works quite well, the line chart from the screenshot below counts the packages in taskviews over time



Using this approach I was able to use one-to-many relationships and avoided the circle in the table relationships.


Hope this gives you and idea

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
dbravo Frequent Visitor
Frequent Visitor

Re: Many to many and two dimensions

Hi TomMartens, thank you for your answer,


I tried your approach but it does not work for me, this is because I forgot explain something important (or I explained it bad). In a location there are n devices of m types and for each device type there are only 1 service, thus in a location we can have certain services depending on the devices for that location only.


With this I need the relationship between locations and devices -type- (for counting how many devices are in a location) and the relationship between services and device types (for counting how many cases with the service/deviceType have been opened). If I activate Locations-Devices I can't count the cases with the service in that location and if I activate Services-Devices I can't count how many devices are on that location.


When I did the cartesian product and the distinct count I got invalid rows (devices with wrong services, locations with wrong devices, etc).


Thank you again.

Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 2,034 guests
Please welcome our newest community members: