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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
emilypoon
Advocate I
Advocate I

USERELATIONSHIP with TEXT

Hi,

 

I would like to retrieve a text with the CALCULATE ( xxx, USERELATIONSHIP (xxx) ) formula.

I understand that USERELATIONSHIP must be used inside a CALCULATE function, but what I want to retrieve is just text only without any calculations.

Does anyone knows how to get the text from a CALCULATE function?

 

 

Best regards,

Emily

 

 

 

12 REPLIES 12
Kpham
Resolver I
Resolver I

I have the same question. And I only find examples where they use the date/time in role playing dimension

Dear experts,

I'm want to use role playing for an other purpose then role playing date's.
I have a FactSales table that contains 2 keys that describes the relation to a DimensionCustomer table. Let's see one is the ship-to-party(SHP) and the other one is the sold-to-party(SDP). So the fact table has two keys KeySHP and KeySDP.
I can create one active relation to the Customer Dimension and one inactive one.

Now I want to create to measures to be able to create a visual with:
Sales Amount; Ship-to-Party, Sold-to-Party

I assume I need to make two measure to be able to use Ship-to-Party and Sold-to-Party and I believe it should look like:
Ship-to-Party = Calculate(Max(DimensionCustomer[Ship-to-Party]), USERELATIONSHIP(DimensionCustomer[KeySHP], FactSales[KeySHP])

Is it correct to use role playing dimensions in this way?

Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Here is an example.

 

Master Table

Column 1: TableID

Column 2: POLCountryName

 

Child Table

Column 1: POLCountryName

Column 2: Count_POLCountry

 

I want to create a MEASURE to retrieve the POLCountryName from the ChildTable so that the POLCountryName can be displayed into a map (using MapBox plug-in) as a Tooltip.

Since my data must be linked by USERELATIONSHIP function, I cannot simply drag & drop POLCountryName from the MotherTable into the tooltip of the map because it doesn't give me the correct result.

In addition, the map tooltip only allow to display AGGREGRATE function (first / last / count distinct / count) and there is no option for "Don't summarize" as it doesn't appear in the right click option, so I tried to create a ChildTable and see whether I can use CALCULATE (xxx , USERELATIONSHIP (xxx) ) to retrieve the POLCountryName from the ChildTable.

 

Is there any other ways that can help me to display the correct POLCountryName in the map in consideration of the USERELATIONSHIP?

 

1.PNG

 

2.PNG

Hi,

Does this work?

=CALCULATE(VALUES('Master Table'[PolCountryName]),USERELATIONSHIP('Child Table'[PolCountryName],'Master Table'[PolCountryName]))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, this solved my problem similar to OP's thank you

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

No, it didn't work and not able to display what I need.

This formula only works at modelling level, but not at visual level.

In addition, if I pull this new measure into the tooltip of MapBox, the tooltip still limits to the aggregate functions only (first / last / count distinct /count).

 

Best regards,

Emily

So you mean that you get the correct answer with my formula when you drag my formula to a Table visual, but not when you drag it to the tool tip section.  Am i correct?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes you are correct.

Any workaround solutions to fix this problem?

Thanks!

That is very strange.  Sorry i do not know why that is happening.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi I have a similar problem with using USERRELATIONSHIP with text columns. Trying to match online comments with locations, and tehre is already a connection with Date Table. So I use relationship dax to be able to use location slicer. Here is the Dax but it gives an error of multiple values was supllied where a single value was expected. Could you help me on that please, thanks

Comments Locations = CALCULATE(VALUES('Reviews'[Comment]),USERELATIONSHIP(Location[Storefront], 'Reviews'[Store]))

Hi,

Replace VALUES() with MAX()


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors