Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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!

 

bandermansor_0-1649073649579.png

 

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
v-kkf-msft
Community Support
Community Support

Hi @Anonymous ,

 

The following figure is an example.

 

vkkfmsft_0-1649311755352.png


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

vkkfmsft_1-1649311811342.png

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.

View solution in original post

5 REPLIES 5
v-kkf-msft
Community Support
Community Support

Hi @Anonymous ,

 

The following figure is an example.

 

vkkfmsft_0-1649311755352.png


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

vkkfmsft_1-1649311811342.png

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?

Greg_Deckler
Super User
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? 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

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

I got unexpected results 

 

bandermansor_0-1649151572971.png

 

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

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

bandermansor_1-1649151677485.png

 

 

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"

 

bandermansor_0-1649150861078.png

 

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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