Accessing measures from another table

[Anonymous]

General info:
- Table A contains [Product codes], for each [Product code] there can be up to several [Material codes].

- Each of those [Material codes] connects with a dictionary Table B (1 to Many) where it has exactly one [Container code].

- Each [Container code] connects with Table C (1 to many) where it has several measures built(code below).

Problem:

- for each [Product code] i need to access measures. Model seems to be very heavy and doesn't produce wanted outcome. I would appreciate help and suggestions on how to write formula that can access it directly from table A.

Example of a measure:
Purpose of this formula is to find top selling customer, by pieces, for each [Container code].

Top sold customer=

VAR max_value =

CALCULATE ( MAX ( 'Sales Activity'[Pieces] ), ALLEXCEPT ( 'Sales Activity','Sales Activity'[Container Code]) )

RETURN

CALCULATE (

MAX ( 'Sales Activity'[Customer] ),

FILTER ( ALLEXCEPT ('Sales Activity','Sales Activity'[Container code] ), 'Sales Activity'[Pieces] = max_value )

)
Table A

Product Code	Material code
a_10	mat_1
a_10	mat_2
a_20	mat_1

Table B

Material code	Container code
mat_1	cont_23
mat_2	cont_42
mat_3	cont_11

Table C

Container code	pieces sold	Cust. Name	Top sold Cust.
cont_23	10	customer_A	customer_C
cont_23	20	customer_B	customer_C
cont_23	30	customer_C	customer_C
cont_42	15	customer_B	customer_B

Desired outcome:

Product code	Material code	Top sold Cust.
a_10	mat_1	customer_C
a_10	mat_2	customer_B
a_20	mat_1	customer_C

[CheenuSing]

Hi @Anonymous ,

Table C does not have materialcode sold.

The output desired row  can not be achieved without material code in Table C

a_10

mat_2

customer_B

Please clarify

Cheers

CheenuSing

[Anonymous]

Hey @CheenuSing, thank You for replying,

I am not sure I understand your question. Desired outcome table gets its data through relatioships. If you refer to measure, I had to change names of some of the columns to make more sense here, but I think everything necessary is here unless you can tell more if I am wrong.

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if you want to get above result , you also could could create relationship between these tables like below, then it should work.

 By the eay, if this is not what you want, please correct me and inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hey Zoe,

Thank you for replying.

Although small relationship like this one looks ok, the roblem was with measures inside Table C transfered to Table A.

Purpose of those measures was to extract value, for a record that had maximum number of pieces in a group by code.

With a lot of digging a found a solution to my problem. I had to create new table based on Table.Max() and then link it together. It works fast and does exactly what needed. I am leaving a link to an article that helped me.

https://excelgorilla.com/power-bi/power-query/reach-underlying-rows-using-table-max/

Thank you for trying to help 🙂