Union of two tables with no relation

[jkay27]

Hi,

Hoping there's a simple explanation to this - I have two tables, one containing two columns: "company" and "basis". The other table contains a single "basis" column. The first table's data is created via the DAX below, and is filtered based on a 1:1 cardinality between Overview and the EAD table (and a slicer). 

EAD = SELECTCOLUMNS('Overview',"Company",Overview[Company],"Basis",Overview[Basis])

1st table (EAD)

Company1 Basis1

Company2 Basis2

Company 3 Basis3

2nd table (Basis List)

Basis 4

Basis 5

Basis 6

I would effectively like to select the "basis" column from table 1 and table 2, then union them, but can't figure out a way to ensure I don't lose my filtering in table 1, as table 2 has no relation to table 1 nor the base "overview" table.

Desired output

If company1 selected:

Basis1

Basis 4

Basis 5

Basis 6

Does anyone know how I can achieve this?

Many thanks

Joel

[v-frfei-msft · Accepted solution]

Hi @jkay27,

Please check the following steps as below.

1. Create a calculated table as below.

ba = UNION(VALUES(Basis[Basi]),VALUES(EAD[Basis]))

2. Create a measure and filter the table visual using the measure, just make the measure is 1.

Measure = var va = MAX(ba[Basi])
var sele = CALCULATE(MAX(EAD[Basis]),ALLSELECTED(EAD))
var al = VALUES(Basis[Basi])
return
IF(va in al || sele =va ,1,0)

Then we can get the result as below.

For more details, please check the pbix as attached.

Regards,

Frank

[jkay27]

@v-frfei-msft many thanks for this. I think I follow how your example is working, but when I line it up to mine (please see attached test) it does not work for some reason.

You will see that company1 is assigned "Revenue" as measure and company2 "Gav". However even if one is selected in the slicer, both GAV and Revenue are returned in the "FINAL" table. Can you see why this is? It looks otherwise exactly the same to me as yours.

Thanks again

[v-frfei-msft]

Hi @jkay27,

To create a measure not a calculated column.

Measure Found2 = var va = MAX(FINAL[Measure])
var selected = CALCULATE(MAX('Overview'[Measure]),ALLSELECTED('Overview'))
var allMeasures = VALUES('Exit Assumptions'[Measure])
return
IF(va in allMeasures || selected = va ,1,0)

For more details, please check the pbix as attached.

Regards,

Frank

[jkay27]

Great, thanks Frank - didn't realised I'd clicked the wrong button. This works perfectly.