cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stefani_vileva
Resolver I
Resolver I

Use relationship as measure not working

Hello everyone,

 

I am struggling with the userelationship function which is not working as I have planed.

My model is looking like this:

- Products table - which contains all of the details of the products, id, name, package_id, box_id and so on.

- Packages - which contains all of the packing types, pack_id, name (here is also the box_id included).

- Clients - which contains all of the clients plus the id of the products that they can buy.

 

I want to create one matrix for a chosen customer to show the following information:

 Product ID, Product name, Box name, Pack name

 

Currently my relationships between the tables are like this Clients with Products (M:1), Products with Packages (M:1) two times but only one of the relationship is active (package_id). When I try to calculate the name of the Box_id using the following dax:

Box = CALCULATE(VALUES(Packages[Name]), USERELATIONSHIP(Products[Box_ID],Packages[Pack_ID]))

there is a problem saying that A table of multiple values was supplied where a single value was expected, but all of the relationships between products and packages is M:1.

 

Can you please give me directions what could be the problem or how could I solve it?

 

Thank you very much.

 

Kind regards,

Stefani

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@stefani_vileva 

Box = 
CALCULATE(
    SELECTEDVALUE(Packages[Name]),
    USERELATIONSHIP(Products[Box ID],Packages[Pack ID]), 
    CROSSFILTER(Products[Box ID],Packages[Pack ID], Both),
    CROSSFILTER(Clients[Product ID], Products[Product ID], Both)
)

View solution in original post

10 REPLIES 10
CNENFRNL
Super User
Super User

From your description of the relationship between Products *:1 Packages, I really doubt your measure could work as desired even if it's activated with USERELATIONSHIP.

Normally, filtering propagates from 1 side to * side through relationship; thus, in your case, from Packages to Products. Expanded table or CROSSFILTER() come into play if a reverse filtering is desired.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

Thanks @CNENFRNL for the fast reply. Actually, with the expanding I have tried and it is working, but I thought this was a better way and that is why I wanted to solve it. 

SpartaBI
Community Champion
Community Champion

@stefani_vileva without getting into your model, just looking at your code. The issue is not USERELATIONSHIP. It is the values funcation. You can't return values when there is more than one. Use this:

 

Box = CALCULATE(SELECTEDVALUE(Packages[Name]),USERELATIONSHIP(Products[Box_ID],Packages[Pack_ID]))


SELECTEDVALUE(Packages[Name]) is like writing IF(HASONEVALUE(Packages[Name]), VALUES(Packages[Name]),BLANK()

If this solved your question, please mark this as a solution for community visabilty

 

Thanks @SpartaBI for the fast answer. Unfortunately using your suggested idea it is returning the package name and not the box name even if I have used box_id in the userelationship. I really don't know what is happening.

 

I just copied your code and modified the reason for the error message you got. Whatever it is you want to bring there, just wrap it with SELECTEDVALUE and not VALUES 

SpartaBI
Community Champion
Community Champion

 

Box = CALCULATE(SELECTEDVALUE(YourTable[Box Name Column]),USERELATIONSHIP(Products[Box_ID],Packages[Pack_ID]))

 

In this case I got all of the possible combinations of it. I have attached a file with the example file to see it: File 

@stefani_vileva that is another issue 🙂 The reason for the error was what I wrote. I will take a look at your file

SpartaBI
Community Champion
Community Champion

@stefani_vileva 

Box = 
CALCULATE(
    SELECTEDVALUE(Packages[Name]),
    USERELATIONSHIP(Products[Box ID],Packages[Pack ID]), 
    CROSSFILTER(Products[Box ID],Packages[Pack ID], Both),
    CROSSFILTER(Clients[Product ID], Products[Product ID], Both)
)

Thank you @SpartaBI. Your solution works!

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors