Use relationship as measure not working

[stefani_vileva]

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

[CNENFRNL]

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.

[stefani_vileva]

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]

@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

[stefani_vileva]

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.

[SpartaBI]

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]

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

[stefani_vileva]

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 

[SpartaBI]

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

[SpartaBI · Accepted solution]

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

[stefani_vileva]

Thank you @SpartaBI. Your solution works!