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 all
I have three table like that.
1-Table1
Customer | ValueA |
A | 2 |
B | 3 |
C | 45 |
D | 4 |
C | 3 |
B | 1 |
A | 2 |
C | 45 |
D | 7 |
E | 0 |
B | 8 |
C | 0 |
A | 3 |
E | 0 |
F | 53 |
T | 21 |
N | 2 |
B | 0 |
C | 8 |
2-Table2
Customer | ValueB |
A | 10 |
B | 51 |
A | 44 |
B | 48 |
C | 0 |
D | 76 |
E | 81 |
F | 6 |
T | 76 |
N | 0 |
N | 47 |
T | 80 |
F | 58 |
F | 66 |
E | 64 |
E | 0 |
D | 75 |
C | 37 |
B | 6 |
B | 21 |
A | 4 |
A | 53 |
A | 0 |
C | 20 |
E | 12 |
T | 69 |
N | 21 |
N | 55 |
N | 0 |
3-Table3
Customer | CustomerNo |
A | 1 |
B | 2 |
C | 3 |
D | 4 |
E | 5 |
F | 6 |
T | 7 |
N | 8 |
Table1 and Table2 were joined to Table3 on Customer Columns.
I want to show this on matrix visual.
Table3.Customer - ValueA - ValueB
I'm applying a visual filter to ValueA like "Show items when the value: is not 0"
Then when i try to apply topN filter to "Table3.Customer with sum of ValueB Top2" it only shows 1 rows.
Then i was tried to use RANKX function.
I was created this two measures.
Measure = RANKX(ALLSELECTED(Table3);[Measure 2];;DESC;Dense)
Measure 2 = SUM(Table2[ValueB])
Then i was added visual filter with [Measure] for show the value <=2 but i got same result.
Is there anyone have idea about this situation
Solved! Go to Solution.
@Anonymous
The underlying cause of this behaviour is that Visual Level Filters are computed independently, then intersected (you can use DAX Studio "All Queries" to verify this).
In your example:
Unfortunately there's no way of telling Power BI the order in which to apply the Visual Level filters.
One solution is to create an awkward-looking measure like below that does the filtering in sequence, and add it as a Visual Level filter set to "= 1"
Customer Filter = // Returns 1 if current customer is in Top 2 for Sum ( ValueB ) after filtering Sum ( ValueA ) <> 0 VAR CurrentCustomer = SELECTEDVALUE ( Table3[Customer] ) RETURN IF ( NOT ISBLANK ( CurrentCustomer ), VAR Customer_Allselected = CALCULATETABLE ( VALUES ( Table3[Customer] ), ALLSELECTED ( Table3 ) ) VAR Customer_Allselected_ValueA_Zero = FILTER ( Customer_Allselected, CALCULATE ( SUM ( Table1[ValueA] ) ) <> 0 ) VAR Customer_Top2_With_ValueA_Zero = TOPN ( 2, Customer_Allselected_ValueA_Zero, CALCULATE ( SUM ( Table2[ValueB] ) ), DESC ) RETURN IF ( CONTAINS ( Customer_Top2_With_ValueA_Zero, Table3[Customer], CurrentCustomer ), 1 ) )
There could be other ways of writing this, but this worked for me in a model using your sample data.
Regards,
Owen
@Anonymous
The underlying cause of this behaviour is that Visual Level Filters are computed independently, then intersected (you can use DAX Studio "All Queries" to verify this).
In your example:
Unfortunately there's no way of telling Power BI the order in which to apply the Visual Level filters.
One solution is to create an awkward-looking measure like below that does the filtering in sequence, and add it as a Visual Level filter set to "= 1"
Customer Filter = // Returns 1 if current customer is in Top 2 for Sum ( ValueB ) after filtering Sum ( ValueA ) <> 0 VAR CurrentCustomer = SELECTEDVALUE ( Table3[Customer] ) RETURN IF ( NOT ISBLANK ( CurrentCustomer ), VAR Customer_Allselected = CALCULATETABLE ( VALUES ( Table3[Customer] ), ALLSELECTED ( Table3 ) ) VAR Customer_Allselected_ValueA_Zero = FILTER ( Customer_Allselected, CALCULATE ( SUM ( Table1[ValueA] ) ) <> 0 ) VAR Customer_Top2_With_ValueA_Zero = TOPN ( 2, Customer_Allselected_ValueA_Zero, CALCULATE ( SUM ( Table2[ValueB] ) ), DESC ) RETURN IF ( CONTAINS ( Customer_Top2_With_ValueA_Zero, Table3[Customer], CurrentCustomer ), 1 ) )
There could be other ways of writing this, but this worked for me in a model using your sample data.
Regards,
Owen
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |