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

Many to many and two dimensions

Hi,

 

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

 

Simplifyng the model, the data is this:

 

DataExample.png

 

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.

 

Relationships.png

 

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:

 

MatrixExample.png

 

Please could you help me?

Thanks In Advance.

2 REPLIES 2
Super User
Super User

Re: Many to many and two dimensions

Hey,

 

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

2017-07-29_8-30-40.png

 

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 you 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

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 4,041 guests
Please welcome our newest community members: