Creating a Virtual Table between two tables with inactive relationship

DAX Calculations
#1

Hi All,

The below sample model shows the inactive relationship between table 1 & 2.

image
image802×525 14.9 KB

I would like to create a virtual table with DAX that includes the following columns:

1- “Matching”: If Table1[id] = Table2[id] then “Matched”, else “Not Matched”
2- “Col 1” from Table 1 & 2
3- “Col 2” from Table 1 &2
4- “Date”
5- “Table” to show with table

image

Sample file attached.Test Virtual Table.pbix (84.7 KB)

Many Thanks.

#2

Hi @Hesham,

I’m unclear about your requirement, if you want to create and display the depicted table.
You can achieve that in Power Query or with a DAX table but, generally speaking, not a virtual table…

Please review this video by Brian, that illustrates it best.

this is code for a DAX table.

image
image653×561 22.3 KB

.

or with Power Query

let
    BufferedTable = Table.Buffer( Source[[ID], [Tbl]] ),
    Source = Table.Combine({Table1, Table2}),
    #"Added Custom" = Table.AddColumn(Source, "Matched", each if Table.IsEmpty( Table.SelectRows( BufferedTable, (OT) => [ID] = OT[ID] and [Tbl] <> OT[Tbl] ))= true then "Not Matched" else "Matching" )
in
    #"Added Custom"

image
image1009×270 7.57 KB

.
Here’s your sample file. Test Virtual Table.pbix (99.1 KB)
I hope this is helpful

1 Like
#3

Hi @Melissa,

Thanks for the prompt response.

I have merged the two tables in my original model with PQ and added the new custom columns. Still, I was interested in finding other ways, especially when measuring the model’s performance (more like comparing both models).

Apologies for my rushed question. I was looking for a DAX Measure instead of a DAX table to maintain the file size. I planned to utilize the measure, to derive calculation based on the new columns.

That said, I will use the above as a foundation and play around to get the result.

This is really helpful. Thanks again.