The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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.
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.
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?
@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?
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
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"
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
8 | |
8 |