Overwrite data from different fact table when available

[richard-powerbi]

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

[AlB]

Hi @richard-powerbi 

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 

[richard-powerbi]

@AlB Thanks! Is a measure also possible if I just want to return the Value?

[AlB]

@richard-powerbi 

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 

[richard-powerbi]

Let's say I have a visual and I have Id from the dimension on the axis, and I want to show Value like it is in Table 3 in the Values of the visual. I think a measure would then be more suitable than a whole table?

[AlB · Accepted solution]

@richard-powerbi 

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 

[speedramps]

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"

[richard-powerbi]

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.

[Anonymous]

You get a PQ solution because DAX solutions will be slower if you start creating measures like the above. As for tables.... yes, you can create them in DAX but it's much better to prepare your data in advance, before it gets loaded into the model. To calculate moderate to large DAX tables it takes much more time than in M. And if I were you, I wouldn't even try to calculate tables of the same cardinality as the fact table through DAX.

Best
D

[speedramps]

Sorry Richard

I did wonder that, but it did look like a classic case where a M solutions was needed.

🤐