cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Anonymous
Not applicable

## Can anyone explain this formula ? about "OR" function

Hi fellowes,

Can anyone please explain this highlighted formula below as it's about using it in a matrix sliced by two slicer like below:

what does it mean ? I couldn't understand it!

OR 2 :=
VAR CategoriesEducations =
CALCULATETABLE (
SUMMARIZE ( Sales, 'Product'[Category], Customer[Education] ),
ALL ( 'Product'[Category] ),
ALL ( Customer[Education] )
)
VAR CategoriesEducationsSelected =
FILTER (
CategoriesEducations,
OR (
'Product'[Category] IN VALUES ( 'Product'[Category] ),
Customer[Education] IN VALUES ( Customer[Education] )
)
)
VAR Result =
CALCULATE ( [Sales Amount], CategoriesEducationsSelected )
RETURN
Result

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

The following figure is an example.

1. Because the function ALL is used and the direction of filtering between tables is "Single",

the virtual table returned by the variable CategoriesEducations

``````VAR CategoriesEducations =
CALCULATETABLE (
SUMMARIZE ( Sales, 'Product'[Category], Customer[Education] ),
ALL ( 'Product'[Category] ),
ALL ( Customer[Education] )
)``````

contains the following data:

 Category Education A X B X A Y B Y C Y A Z B Z C Z

2. Then the virtual table is filtered:

``````VAR CategoriesEducationsSelected =
FILTER (
CategoriesEducations,
OR (
'Product'[Category] IN VALUES ( 'Product'[Category] ),
Customer[Education] IN VALUES ( Customer[Education] )
)
)``````

Where VALUES ( 'Product'[Category] ) returns {"A"}, and VALUES ( Customer[Education] ) returns {"Y"}. Use OR to filter the rows in virtual table that have category A or education Y:

 Category Education A X A Y B Y C Y A Z

3. Then sum the amount in those rows.

``````VAR Result =
CALCULATE ( [Sales Amount], CategoriesEducationsSelected )``````

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Community Support

Hi @Anonymous ,

The following figure is an example.

1. Because the function ALL is used and the direction of filtering between tables is "Single",

the virtual table returned by the variable CategoriesEducations

``````VAR CategoriesEducations =
CALCULATETABLE (
SUMMARIZE ( Sales, 'Product'[Category], Customer[Education] ),
ALL ( 'Product'[Category] ),
ALL ( Customer[Education] )
)``````

contains the following data:

 Category Education A X B X A Y B Y C Y A Z B Z C Z

2. Then the virtual table is filtered:

``````VAR CategoriesEducationsSelected =
FILTER (
CategoriesEducations,
OR (
'Product'[Category] IN VALUES ( 'Product'[Category] ),
Customer[Education] IN VALUES ( Customer[Education] )
)
)``````

Where VALUES ( 'Product'[Category] ) returns {"A"}, and VALUES ( Customer[Education] ) returns {"Y"}. Use OR to filter the rows in virtual table that have category A or education Y:

 Category Education A X A Y B Y C Y A Z

3. Then sum the amount in those rows.

``````VAR Result =
CALCULATE ( [Sales Amount], CategoriesEducationsSelected )``````

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks V-kk

It become more clear now but not too much.

I have a question, why do we use this kind of formula? why do we need it?

Super User

@Anonymous Well, what it is doing is filtering your CategoriesEducations table var and keeping all rows in that table that either have the Category column in the currently selected values for product category or Education column in the currently selected values for education. However, I have doubts about that formula, does it actually work?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I got unexpected results

In the first matrix, I selected "Sales Amount" measure as VALUE.

but in the second martix I selected the following measure in the VALUE

Anonymous
Not applicable

Hi @Greg_Deckler , Actually I got this formula from the father of this language book "Alberto", see below:

If you mean is it work without errors, the answer is yes?

But if you mean if that formula logically work, yes.

I just want to understand why we using "OR"

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors