cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zyg_D
Responsive Resident
Responsive Resident

Making the lookup work.

This is my data and model:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lECYTMzM6VYHaCAEVTA3NwcLGAE4oAELSwsIAJGUAFLS0ul2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, Parent = _t, AttributeChild = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Child", Int64.Type}, {"Parent", Int64.Type}, {"AttributeChild", Int64.Type}})
in
    #"Changed Type"
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0NFSK1YlWMgKyjYyMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, AttributeParent = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", Int64.Type}, {"AttributeParent", Int64.Type}})
in
    #"Changed Type"
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nci7CQBBCEDBXow30cBPLSLHwpVw/XO8wGyYblFVOfLe7z5wTouZbUHK3bcgFRFbkMrMLUhV1Rac+QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, AttributeData = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", Int64.Type}, {"AttributeData", type text}})
in
    #"Changed Type"

data.JPG
model.JPG

 

I want to add a column to Table1 which would show the Child's AttributeData. Like this: 
desired_result.JPG

How to do it while keeping two-directional cross filtering??? I could make the incative relationship single-direction, but not the active ones!

These all formulas fails miserably: 

CALCULATE(MAX(Table3[AttributeData]),USERELATIONSHIP(Table1[AttributeChild],Table3[Attribute]))

CALCULATE(SELECTEDVALUE(Table3[AttributeData]),USERELATIONSHIP(Table1[AttributeChild],Table3[Attribute]))

LOOKUPVALUE(Table3[AttributeData],Table3[Attribute],Table1[AttributeChild])

MAXX(
    CALCULATETABLE(
        Table3,
        USERELATIONSHIP(Table1[AttributeChild],Table3[Attribute])
    ),
    Table3[AttributeData]
)

CALCULATE(
    MAX(Table3[AttributeData]),
    TREATAS(
        VALUES('Table1'[AttributeChild]),
        Table3[Attribute]
    )
) 

 

1 ACCEPTED SOLUTION
AntrikshSharma
Resident Rockstar
Resident Rockstar

Thanks @BA_Pete 

 

@Zyg_D Does the following work for you?

1.PNG2.PNG

Column =
CALCULATE (
    MINX (
        Table1,
        RELATED ( Table3[AttributeData] )
    ),
    USERELATIONSHIP ( Table1[AttributeChild], Table3[Attribute] ),
    ALLEXCEPT ( Table1, Table1[AttributeChild] )
)

View solution in original post

9 REPLIES 9
v-lili6-msft
Community Support
Community Support

hi @Zyg_D 

I don't think you need to create a relationship between table2 and table3, just remove this relationship and keep active for the relationship between table1 and table3.

 

or just use lookupvalue function to create a column as below:

Calculate Column = LOOKUPVALUE(Table3[AttributeData], Table3[Attribute], Table1[Attribute Child])

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zyg_D
Responsive Resident
Responsive Resident


@v-lili6-msft wrote:

hi @Zyg_D 

I don't think you need to create a relationship between table2 and table3, just remove this relationship and keep active for the relationship between table1 and table3.

or just use lookupvalue function to create a column as below:

 

Calculate Column = LOOKUPVALUE(Table3[AttributeData], Table3[Attribute], Table1[Attribute Child])

 

Regards,

Lin


Thanks, Lin (@v-lili6-msft), for digging into the problem. the LOOKUPVALUE that you suggested is exactly like one of my tries which you can find above and which failed in my case. However, the suggestion to reorder my relationships may work too. I thought about it myself, it's just that I fear I would need to rework many things which are already dependent on these relationships. 

AntrikshSharma
Resident Rockstar
Resident Rockstar

Thanks @BA_Pete 

 

@Zyg_D Does the following work for you?

1.PNG2.PNG

Column =
CALCULATE (
    MINX (
        Table1,
        RELATED ( Table3[AttributeData] )
    ),
    USERELATIONSHIP ( Table1[AttributeChild], Table3[Attribute] ),
    ALLEXCEPT ( Table1, Table1[AttributeChild] )
)
Zyg_D
Responsive Resident
Responsive Resident


@AntrikshSharma wrote:

Thanks @BA_Pete 

 

@Zyg_D Does the following work for you?

1.PNG2.PNG

 

Column =
CALCULATE (
    MINX (
        Table1,
        RELATED ( Table3[AttributeData] )
    ),
    USERELATIONSHIP ( Table1[AttributeChild], Table3[Attribute] ),
    ALLEXCEPT ( Table1, Table1[AttributeChild] )
)

 


@AntrikshSharma  - You are the true Power BI hero, man! Many superusers and Microsoft staff failed... and you did it! It's increadible! Too bad that I can only give one thumb up 🙂 Cheers! 

PaulDBrown
Super User
Super User

@Zyg_D 

For a measure, try:

 

 

Measure = CALCULATE(MAX(table3[AttributeData]), TREATAS(VALUES(table1[AttributeChild]), table3[Attribute])

 

If you prefer a calculated column for table1:

 

 

Column = LOOKUPVALUE(Table3[AttributeData], Table3[Attribute], Table1[Attribute Child])

 

 

 

result.JPG

 

Btw, why do you need a two-directional cross filtering in many-to-one relationships?

 





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.






@Zyg_D ,

Below is the  screen shot only based on relationship.

Step 1: Create the relationship like below screen shot.

Step 2: Directly take all columns from table 1 and simply take AttributeData from Table3. (refer screen shot 2).

Step 3: If you want to achieve using a dax so write column : CALCULATE(MAX(Table3[AttributeData])). (Refer screen shot 3).

 

1 (Relationship)1 (Relationship)223 (DAX Column)3 (DAX Column)

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
BA_Pete
Super User
Super User

Hi @Zyg_D ,

 

If you want a physical column in Table 1 then just perform a merge in Power Query on Table1[AttributeChild] and Tble3[Attribute].

 

Pete

Zyg_D
Responsive Resident
Responsive Resident


@BA_Pete wrote:

Hi @Zyg_D ,

If you want a physical column in Table 1 then just perform a merge in Power Query on Table1[AttributeChild] and Tble3[Attribute].

Pete


Thank you, Pete. This would work, but not always (e.g. some tables or columns in question may be calculated in DAX). So, I would like to receive an answer in DAX or a decent explanation of why a simple lookup is nearly impossible in DAX. 

@Zyg_D 

 

Good point re: calculated columns etc.

I'll pass over to the DAX heroes:

 

@Greg_Deckler  @AntrikshSharma  @vanessafvg  @PaulDBrown  @tex628  @EricHulshof  @Tahreem24  @amitchandak  @dedelman_clng  @OwenAuger  @mahoneypat  @nandukrishnavs  @TomMartens  @mwegener  @edhans 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!