Many to many and two dimensions

[dbravo]

Hi,

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.

[TomMartens]

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:

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

[dbravo]

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.