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
I'm new to PowerBI (about 54 minutes into it) and struggling to figure out how to set up a filter that mimicks the following SQL:
SELECT ...
WHERE tableA.field1 = 1
AND (tableB.field2 in ('A', 'B') OR tableC.field3 in ('C', 'D', 'E') )
I was trying to set up a calculated field that represents the OR condition ... but even that is proving quite hard to figure out without any decent clues.
Creating the filters I have figured out - it is just the "OR" involving fields from different tables that I am stuck on.
Solved! Go to Solution.
Hi evoges,
Based on your description, you want to achieve a new table which meet the condition in SQL script you have written, right?
Assume Table2 and Table3 have “One to one” or “One to many” relationship with table1.In this condition, to achieve your requirement, please follow steps below:
Calculate field = IF(AND(Table1[field1] = 1, OR(RELATED(Table2[field2]) in {"A", "B"}, RELATED(Table3[field3]) in {"C", "D", "E"})), TRUE(), FALSE())
3.Use the calculate field to create a slicer, the result is as below and you can refer to PBIX file:
https://www.dropbox.com/s/qffdzbic1t5hf3s/For%20evoges.pbix?dl=0
Best Regards,
Jimmy Tao
Hi evoges,
Based on your description, you want to achieve a new table which meet the condition in SQL script you have written, right?
Assume Table2 and Table3 have “One to one” or “One to many” relationship with table1.In this condition, to achieve your requirement, please follow steps below:
Calculate field = IF(AND(Table1[field1] = 1, OR(RELATED(Table2[field2]) in {"A", "B"}, RELATED(Table3[field3]) in {"C", "D", "E"})), TRUE(), FALSE())
3.Use the calculate field to create a slicer, the result is as below and you can refer to PBIX file:
https://www.dropbox.com/s/qffdzbic1t5hf3s/For%20evoges.pbix?dl=0
Best Regards,
Jimmy Tao
I asume your in Dax (not M/Query Editor)
There is a DAX function OR as well as AND
https://msdn.microsoft.com/en-us/library/ee634951.aspx
e.g.
FilteredTable = FILTER ( MyTable, AND ( OR ( MyTable[Column1] = "1", MyTable[Column2] = "x" ), MyTable[Column3] = "y" ) )
See some examples of filters here
https://www.sqlbi.com/articles/from-sql-to-dax-filtering-data/
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |