Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
trailmax
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
v-yulgu-msft
Employee
Employee

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

5 REPLIES 5
v-yulgu-msft
Employee
Employee

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.

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

Anonymous
Not applicable

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
Impactful Individual
Impactful Individual

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.

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

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!

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.