Forum Discussion

trailmax's avatar
trailmax
Frequent Visitor
7 years ago
Solved

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 ve...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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-data-in-single/m-p/400409

 

 

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