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

Multiple columns join/goup

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

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.
5 REPLIES 5
nirvana_moksh Established Member
Established Member

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.

Super User
Super User

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

Highlighted
Community Support Team
Community Support Team

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.
trailmax Frequent Visitor
Frequent Visitor

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!

trailmax Frequent Visitor
Frequent Visitor

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!