Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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
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
Great, thanks Frank - didn't realised I'd clicked the wrong button. This works perfectly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |