Multiple columns join/goup

[trailmax]

Hi

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.

Here is the sample file if you care to play with it: https://www.dropbox.com/s/qmq8netmeeniqt9/Sample.pbix?dl=0

What direction should I look into? 

Thanks in advance!

[v-yulgu-msft · Accepted solution]

Hi @trailmax,

Please try this measure:

sum measure =
CALCULATE (
    SUM ( Service[ServiceLength] ),
    FILTER (
        ALLSELECTED ( Service ),
        Service[Date] = SELECTEDVALUE ( FleetCount[Date] )
            && Service[Location] = SELECTEDVALUE ( FleetCount[Location] )
            && Service[Type] = SELECTEDVALUE ( FleetCount[Type] )
    )
)

Best regards,

Yuliana Gu

[trailmax]

@v-yulgu-msft that was the right combination. I was missing `ALLSELECTED` from my attempts to fix the problem. Thank you!

[Anonymous]

Hi @trailmax

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.

This link will be useful to you.

https://community.powerbi.com/t5/Report-Server/Many-to-Many-Relationship-between-tables-and-show-dat...

P.S: You can connect Many to Many table directly in the latest Power BI, but you wont be able to publish the report tpo Power BI

Service. For that you need to do this:

File > Options and Settings > Options > Preview Features, then select the composite models checkbox.

Thanks
Raj

[nirvana_moksh]

https://www.dropbox.com/s/pxfgq3c7bkmj6c6/Sample.pbix?dl=0

I created a group by and sum by service length table in the file in there, also i created a reference table and hopefully that should help you achieve your solution.

[trailmax]

@nirvana_moksh, @Anonymous I've tried doing the intermediate tables, the problem that I need 3 of them and they are not disjointed - I need Location, Date, Type. Like this:

And when it comes to filters, all three need to work like one table, not 3 disjointed. 

I'll keep this technique in mind for the future - thank you for looking into this!