Reply
Frequent Visitor
Posts: 6
Registered: ‎08-29-2018
Accepted Solution

Multiple columns join/goup

[ Edited ]

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:

2018-09-18 16_29_22-Sample - Power BI Desktop.png

 

Here is service table: 

2018-09-18 16_30_17-Sample - Power BI Desktop.png

 

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!


Accepted Solutions
Community Support Team
Posts: 5,652
Registered: ‎09-21-2016

Re: Multiple columns join/goup

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Established Member
Posts: 267
Registered: ‎02-01-2018

Re: Multiple columns join/goup

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.

Highlighted
Super User
Posts: 668
Registered: ‎11-01-2017

Re: Multiple columns join/goup

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

Community Support Team
Posts: 5,652
Registered: ‎09-21-2016

Re: Multiple columns join/goup

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 6
Registered: ‎08-29-2018

Re: Multiple columns join/goup

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

Frequent Visitor
Posts: 6
Registered: ‎08-29-2018

Re: Multiple columns join/goup

@nirvana_moksh@rajendran 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:

2018-09-19 15_22_22-Sample - Power BI Desktop.png

 

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!