Forum Discussion
Overwrite data from different fact table when available
How do I create a measure or a table with DAX to get calculated Table 3?
And how would I do it if I wanted just the measure for Value of Table 3?
Assuming I have common dimensions.
So when data exists in Table 2, it should overwrite data in Table 1.
| Table 1 | Table 2 | Table 3 | |||||
| Id | Value | Id | Value | Id | Value | ||
| 1 | A | 2 | X | 1 | A | ||
| 2 | B | 3 | Y | 2 | X | ||
| 3 | C | 5 | Z | 3 | Y | ||
| 4 | D | 4 | D | ||||
| 5 | E | 5 | Z |
Assuming the field in the visual is the TableX[Id]:
Measure = VAR valT2_ = LOOKUPVALUE ( Table2[Value], Table2[Id], SELECTEDVALUE ( TableX[Id] ) ) RETURN IF ( ISBLANK ( valT2 ), LOOKUPVALUE ( Table1[Value], Table1[Id], SELECTEDVALUE ( TableX[Id] ) ), valT2_ )Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
9 Replies
Please consider this solutioin and leave kudos.
Create a query with a merge left join and then a conditional column.
Note this is assuming that Table1 will always have a row for every ID.
If it hasn't then you will need to tweak the query accordingly.
Ulet
Source = Table.NestedJoin(Table1, {"Id"}, Table2, {"Id"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Id", "Value"}, {"Table2.Id", "Table2.Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Table2",{{"Value", "Table1.Value"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Value", each if [Table2.Id] = null then [Table1.Value] else [Table2.Value]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Table1.Value", "Table2.Id", "Table2.Value"})
in
#"Removed Columns"I know this.... but why do I always get a PQ solution when I ask a DAX solution? 😞 I want to keep them as separate tables because they are different processes.
Sorry Richard
I did wonder that, but it did look like a classic case where a M solutions was needed.
🤐
Calculated table in DAX:
Table3 = ADDCOLUMNS ( UNION ( DISTINCT ( Table1[Id] ), DISTINCT ( Table2[Id] ) ), "Value", VAR valT2_ = LOOKUPVALUE ( Table2[Value], Table2[Id], [Id] ) RETURN IF ( ISBLANK ( valT2 ), LOOKUPVALUE ( Table1[Value], Table1[Id], [Id] ), valT2_ ) )Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB Thanks! Is a measure also possible if I just want to return the Value?
A measure tobe used where and how? and to yield what result exactly? You talked about a calcualted table. Try to make it clearer with an example
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
