topic Re: Overwrite data from different fact table when available in DAX Commands and Tips

Overwrite data from different fact table when available

richard-powerbi — Wed, 03 Jun 2020 12:09:26 GMT

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

Re: Overwrite data from different fact table when available

speedramps — Wed, 03 Jun 2020 13:57:16 GMT

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"

Re: Overwrite data from different fact table when available

richard-powerbi — Wed, 03 Jun 2020 14:06:39 GMT

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.

Re: Overwrite data from different fact table when available

speedramps — Wed, 03 Jun 2020 14:04:47 GMT

Sorry Richard

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

🤐

Re: Overwrite data from different fact table when available

AlB — Wed, 03 Jun 2020 14:10:02 GMT

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

Re: Overwrite data from different fact table when available

richard-powerbi — Wed, 03 Jun 2020 14:14:52 GMT

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

Re: Overwrite data from different fact table when available

AlB — Wed, 03 Jun 2020 14:18:26 GMT

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

Re: Overwrite data from different fact table when available

richard-powerbi — Wed, 03 Jun 2020 14:23:46 GMT

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?

Re: Overwrite data from different fact table when available

Anonymous — Wed, 03 Jun 2020 14:24:34 GMT

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

Re: Overwrite data from different fact table when available

AlB — Wed, 03 Jun 2020 14:27:27 GMT

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