Forum Discussion

richard-powerbi's avatar
richard-powerbi
Post Patron
6 years ago
Solved

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 ex...
Need Help
  • AlB's avatar
    AlB
    6 years ago

    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's avatar
speedramps
Super User
6 years ago

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's avatar
    richard-powerbi
    Post Patron
    6 years ago

    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.

    • speedramps's avatar
      speedramps
      Super User
      6 years ago

      Sorry Richard

       

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

       

      🤐

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      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