Multiple filter conditions

Anonymous · ‎04-17-2019

Hi,

I got the table DimCompanyHis (it contains more columns but these are not relevant for the filtering).
company category and company subcategory are textual columns.

Company name	Company category	Company subcategory
A	1	25
B	1	28
C	1	33
D	1	33
E	1	-
F	2	18
G	2	-
H	2	-
I	3	12
J	4	33

I need to filter out companies:

1. Company category = "2"
OR
2. Company category = "1" AND Company subcategory = "33"

This is too much for the regular Power BI filter options so I need DAX.
I tried a query with CALCULATEDTABLE but I got the scalar value vs multiple value error.

Any ideas?
Thx in advance!

parry2k · ‎04-17-2019

@Anonymous try following measure

Your Measure = 
CALCULATE( SUM(Table[Sales]),
Company[Company Category]="2" ||
AND( Company[Company Category] = "1", Company[Compay Subcategory]="33")
)

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎04-17-2019

Why the SUM?
My category and subcategory are textual values (saying this I know that I could have created better example data:-) ).

parry2k · ‎04-17-2019

@Anonymous sum was just to show how to apply or condition in your measures.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎04-19-2019

Hi @parry2k ,

I used the query (and changed the corresponding columns) and got the following error:
"The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

Why can't AND handle this?

parry2k · ‎04-19-2019

@Anonymous missed to add filter

Your Measure = 
CALCULATE( SUM(Table[Sales]),
FILTER(
Company,
Company[Company Category]="2" ||
AND( Company[Company Category] = "1", Company[Compay Subcategory]="33")
)
)

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎05-13-2019

@parry2k I still don't understand what the correct query is and why I need SUM.
SUM gives me an error since my data consists of strings instead of numbers.

But if I don't use the SUM I get the following error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".