Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
I want to add a column to Table1 which would show the Child's AttributeData. Like this:
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]
)
)
Solved! Go to Solution.
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
@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 wrote:Thanks @BA_Pete
@Zyg_D Does the following work for you?
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!
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])
Btw, why do you need a two-directional cross filtering in many-to-one relationships?
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).
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
Proud to be a Datanaut!
@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.
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |