Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi~Dear PowerBI community
I met a scenario that got some results I couldn't explain well, making me doute and confused. I think there must be something wrong in my understanding about how CALCULATE merge the filter arguments. I've done all I can but failed to figure it out.
Let me try to show this question, hoping someone would give me some hints.
----
There are two tables : <Dfact> and <Ddim> , and a relationship between them
<Dfact>
ID | SubCategory |
1 | SC1 |
2 | SC1 |
3 | SC2 |
4 | SC2 |
5 | SC3 |
6 | SC3 |
<Ddim>
Category | SubCategory |
C1 | SC1 |
C1 | SC2 |
C2 | SC3 |
C2 | SC4 |
C3 | SC5 |
I've learned that when a table is transformed in a filter, the filter contains the expanded version of the table. Therefore, the following measure calculates the number of <Ddim> referenced in the <Dfact> table:
CALCULATE ( COUNTROWS(Ddim), Dfact)
Then I added an additional column filter argument and got the same result:
CALCULATE ( COUNTROWS( Ddim ), Dfact, Dfact[SubCategory] = "SC1" )
I was trying to make analyses of the filter context and I knew I could explain it as below:
the columns filter used as the second argument is a column not a table, and there's no such a column in the expanded version of <Ddim> table, so the column filter won't filter the <Ddim> table . While the first augument <Dfact> can filter the <Ddim> due to the expanded version of the <Dfact> table.
But things got wired while I analyzed it another way with more detail into the merge of the filter arguments.
I read The Definitive Guide to DAX and it told me these:
Given two filter contexts, A and B, the intersection of A and B is computed by adding the filters in A to the filters in B.
According to these rules, I analysed it as below:
FILTER A (the first augument):
Dfact (expanded version containing all the columns of <Ddim>), a table with many columns
FILTER B (the second augument):
Dfact[SubCategory]="SC1" or FILTER(ALL(Dfact[SubCategory]), Dfact[SubCategory] = "SC1"), a table with only one column
If I add filters B to filters A, I will get the result filter C as below, is it right?
If I were right, the first argument of CALCULATE should be calculated in this new filter context and get a result as 1( only one row contains "SC1" in <Ddim>), which is completely different from the previous result as 3 .
So there must be something wrong in my understanding about how CALCULATE merge the filter arguments, even worse about what the filter really is.
I tried some tests and got these results
The result of test1 is the table <Dfact> with only "SC1" in the rows, identical to the analysis by merge filter argument above.
The result of test2 is the table <Ddim> with the subcategories referenced in the <Dfact> table, identical to the analysis by the expanded talbe and filter propagation.
The result of test3 shows the number of rows of <Dfact> and <Ddim> in the exactly identical filter context.
I thought CALCULATE creates the new filter context before caluclates the first argument, but the result of test3 confused me more, wandering are there two filter contexts affecting the two COUNTROWS respectively ?
Furthermore, I checked the SE queries, there was "[SubCategory] = 'SC1' " in the WHERE statement of the xmSQL of test1, whereas in the xmSQL of test2, "[SubCategory] = 'SC1' " is totally discarded in the 3 SE queries.
I can't get more useful information in the SE query, cause I knew few about it.
I got stuck here.
Please give me some helps.
sorry, made a mistake in one image:
There is only one column in FILTER B as below
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
49 | |
45 | |
20 | |
16 |