Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I Need to filter on 100 values in dataset, How can I do that in DAX
Data:
ID Column1 Column 2 etc...
1 A1
2 B1
3 C1
4 D1
so on ..
How Can I filter records which are in (A1,B1,C1,D1...........Z1) ?
I have 100 categories to filter and there are 900 categories
Solved! Go to Solution.
In DAX, to filter table with "IN" condition, you can only make Column1 match each value and apply OR logic between them.
FILTER(Table,Table[Column1] = A1 || Table[Column1] = B1 || Table[Column1] = C1 || Table[Column1] = D1)
Please also refer to this blog: From SQL to DAX: IN and EXISTS
Regards,
In DAX, to filter table with "IN" condition, you can only make Column1 match each value and apply OR logic between them.
FILTER(Table,Table[Column1] = A1 || Table[Column1] = B1 || Table[Column1] = C1 || Table[Column1] = D1)
Please also refer to this blog: From SQL to DAX: IN and EXISTS
Regards,
First, are the categories really A1, B1, etc. or are you just using that as an example? Second, what are the other categories (like actual names?
A brute force way would be to create a column using nested IF statements (yuck!) or a SWITCH statement (cleaner) that simply returned 1 for included categories and 0 for categories you do not want included. You could then simply filter on that column for all values equal to 1.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |