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
amarsale85
Helper II
Helper II

PATHCONTAINS and Multiple selected value

Hi Experts,

 

i have created below measure to calculate aggregated profit amount for locations at parent level (like Continent, Country, Province) which would be summation of children cities. Our Database stores data at city level only.

 

Parent Profit Amount := 
VAR c = SELECTEDVALUE ( DimLocation[LocationKey] ) 
RETURN 
CALCULATE ( 
SUM ( FactAmount[Profit] ), 
ALL ( DimLocation ), 
PATHCONTAINS ( DimLocation[Path], c ) 
)

 

 

Now the issue is, how can i handle if there are multiple selected values ? Because here, SELECTEDVALUE () function expects a single distinct value and if its given multiple values it doesn't show anything. Suppose below data exists in Database:

 

CountryLocationKeyCityProfit Amount
England10London25
USA11London30
Canada12London45

 

And i just want to show "City" and its "Profit Amount" in chart. 

The above measure won't display anything for Location="London" because for "London" location SelectedValue() function gets 3 locationkey values "10,11,12". So in the chart London isn't shown at all.

 

Please help.

 

Regards,

Amar 

8 REPLIES 8
randymarchena
Frequent Visitor

Hi, did you find a solution to your question?

PaulDBrown
Community Champion
Community Champion

@amarsale85 

Apologies since I'm not familiar with the PATHCONTAINS function, but wouldn't this measure work?

Total by city = CALCULATE([Sum of Profit], ALLEXCEPT(table, table[city]))




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






lbendlin
Super User
Super User

Here are some options:

 

- use MIN/MAX instead of SELECTEDVALUE

- use TOPN(1,FILTERS())

- make your slicer single select.

Thank you for your reply.

Is there a way to take all multiple values and apply it to the measure in PathContains() function?

yes, convert your path into a table variable and then use INTERSECT()

@amarsale85 If you are having trouble with @lbendlin 's suggestion, which is a good one, see Text to Table: https://community.powerbi.com/t5/Quick-Measures-Gallery/Text-to-Table/m-p/1312929#M594


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you lbendlin for your suggestion but it isn't working. Below is my code:

Sum Amount:=
VAR SelectedLocations = 
SELECTCOLUMNS(
VALUES(DimLocation),
"LocationID", DimLocation[LocationID]+0 
)
VAR mytable =
SELECTCOLUMNS (
GENERATE (
ALL(DimLocation),
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( [Calculated Path] ) ),
"PivotPath", PATHITEM ( [Calculated Path], [Value], 1 )
)
),
"LocationID", DimLocation[LocationID]+0,
"PivotPathKey", [PivotPath]
)
RETURN
CALCULATE ( 
SUM(FactAmount[Amount]), 
INTERSECT(mytable,SelectedLocations)
)

This code gives me error.

I used INTERSECT here, but it gives error since these 2 tables have different no. of columns. If i used NATURALINNERJOIN instead of INTERSECT, that gives an error as well.

 

Can you suggest what else can be used in place of INTERSECT() ?

 

Regards,

Amar

In the Intersect wrap your "myTable"  table into another SelectColumns() so that it matches the structure of the other table.

Helpful resources

Announcements
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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

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.