I'm new to PowerBI, though I have developer background. I'm stuck now on a report that has 2 columns: one with vehicles count by type, location and month and another with days in service of vehicles by location, month, type of vehicle and type of service. I need to produce a monthly ratio of service to number of vehicles. And I need to filter by region/vehicle type and service type.
Here is vehicles count table:
Here is service table:
Now I can't create relationship between the 2 tables because it is not one-to-one and I need to match on multiple columns. I can't just group all the service data by location/type/month, because I need to filter by service type. I've looked at CALCULATE, SUMMARIZE and a host of other stuff but can't come with with a DAX way to do this report.
Had it been SQL, I'd be doing an group by on Service table, getting SUM of ServiceLength for the required service type and then join that to the fleet table on multiple columns. But that does not quite work in DAX.
You can create a bridge table/ relationship table to break the Many to Many relationship with Unique "Location" values, set the relationship & cross filter direction , which will help you to acheibve the requirement.