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, I'm having an issue where the slicer filters the underlying data, but not the matrix itself:
We see that the values are calculated for Q4 2020 - Q2 2021, but the remaining quarters should not even be appearing due to the date range selected by the slicer.
This feels like it should be a rather intuitive feature to use, so I'm not sure what is happening here, and it can be frustrating.
Matrix Settings
Sample Data
Date | ParentGroup | Group | SlicerGroup1 | Return | ReturnsNorm |
31-Jan-20 | PG_1 | PG1_G1 | SG1_1 | 0.10% | 100.10% |
31-Jan-20 | PG_1 | PG1_G2 | SG1_1 | -0.12% | 99.88% |
28-Feb-20 | PG_1 | PG1_G1 | SG1_1 | 0.03% | 100.03% |
28-Feb-20 | PG_1 | PG1_G2 | SG1_1 | 0.67% | 100.67% |
31-Mar-20 | PG_1 | PG1_G1 | SG1_1 | 0.48% | 100.48% |
31-Mar-20 | PG_1 | PG1_G2 | SG1_1 | -0.55% | 99.45% |
30-Apr-20 | PG_1 | PG1_G1 | SG1_1 | -0.13% | 99.87% |
30-Apr-20 | PG_1 | PG1_G2 | SG1_1 | -0.34% | 99.66% |
29-May-20 | PG_1 | PG1_G1 | SG1_1 | 0.00% | 100.00% |
29-May-20 | PG_1 | PG1_G2 | SG1_1 | 0.28% | 100.28% |
30-Jun-20 | PG_1 | PG1_G1 | SG1_1 | 0.34% | 100.34% |
30-Jun-20 | PG_1 | PG1_G2 | SG1_1 | -0.40% | 99.60% |
31-Jul-20 | PG_1 | PG1_G1 | SG1_1 | -0.65% | 99.35% |
31-Jul-20 | PG_1 | PG1_G2 | SG1_1 | 0.76% | 100.76% |
31-Aug-20 | PG_1 | PG1_G1 | SG1_1 | -0.33% | 99.67% |
31-Aug-20 | PG_1 | PG1_G2 | SG1_1 | -0.04% | 99.96% |
30-Sep-20 | PG_1 | PG1_G1 | SG1_1 | 0.12% | 100.12% |
30-Sep-20 | PG_1 | PG1_G2 | SG1_1 | -0.56% | 99.44% |
30-Oct-20 | PG_1 | PG1_G1 | SG1_1 | -0.20% | 99.80% |
30-Oct-20 | PG_1 | PG1_G2 | SG1_1 | -0.06% | 99.94% |
30-Nov-20 | PG_1 | PG1_G1 | SG1_1 | -0.22% | 99.78% |
30-Nov-20 | PG_1 | PG1_G2 | SG1_1 | -0.20% | 99.80% |
31-Dec-20 | PG_1 | PG1_G1 | SG1_1 | 0.34% | 100.34% |
31-Dec-20 | PG_1 | PG1_G2 | SG1_1 | -0.74% | 99.26% |
29-Jan-21 | PG_1 | PG1_G1 | SG1_1 | 0.02% | 100.02% |
29-Jan-21 | PG_1 | PG1_G2 | SG1_1 | 0.03% | 100.03% |
26-Feb-21 | PG_1 | PG1_G1 | SG1_1 | -0.59% | 99.41% |
26-Feb-21 | PG_1 | PG1_G2 | SG1_1 | 0.35% | 100.35% |
31-Mar-21 | PG_1 | PG1_G1 | SG1_1 | -0.02% | 99.98% |
31-Mar-21 | PG_1 | PG1_G2 | SG1_1 | -0.30% | 99.70% |
30-Apr-21 | PG_1 | PG1_G1 | SG1_1 | -0.28% | 99.72% |
30-Apr-21 | PG_1 | PG1_G2 | SG1_1 | -0.24% | 99.76% |
30-Apr-21 | PG_2 | PG2_G2 | SG1_1 | -0.10% | 99.90% |
31-Mar-21 | PG_2 | PG2_G2 | SG1_1 | 0.52% | 100.52% |
26-Feb-21 | PG_2 | PG2_G2 | SG1_1 | -0.56% | 99.44% |
29-Jan-21 | PG_2 | PG2_G2 | SG1_1 | 0.20% | 100.20% |
31-Dec-20 | PG_2 | PG2_G2 | SG1_1 | 0.38% | 100.38% |
30-Nov-20 | PG_2 | PG2_G2 | SG1_1 | 0.16% | 100.16% |
30-Oct-20 | PG_2 | PG2_G2 | SG1_1 | 0.07% | 100.07% |
30-Sep-20 | PG_2 | PG2_G2 | SG1_1 | 0.09% | 100.09% |
31-Aug-20 | PG_2 | PG2_G2 | SG1_1 | -0.53% | 99.47% |
31-Jul-20 | PG_2 | PG2_G2 | SG1_1 | 0.69% | 100.69% |
30-Jun-20 | PG_2 | PG2_G2 | SG1_1 | -0.21% | 99.79% |
29-May-20 | PG_2 | PG2_G2 | SG1_1 | 0.08% | 100.08% |
30-Apr-20 | PG_2 | PG2_G2 | SG1_1 | -0.58% | 99.42% |
31-Mar-20 | PG_2 | PG2_G2 | SG1_1 | 0.85% | 100.85% |
28-Feb-20 | PG_2 | PG2_G2 | SG1_1 | 0.11% | 100.11% |
31-Jan-20 | PG_2 | PG2_G2 | SG1_1 | 0.28% | 100.28% |
30-Apr-21 | PG_2 | PG2_G1 | SG1_1 | -0.30% | 99.70% |
31-Mar-21 | PG_2 | PG2_G1 | SG1_1 | -0.31% | 99.69% |
26-Feb-21 | PG_2 | PG2_G1 | SG1_1 | -0.69% | 99.31% |
29-Jan-21 | PG_2 | PG2_G1 | SG1_1 | 0.48% | 100.48% |
31-Dec-20 | PG_2 | PG2_G1 | SG1_1 | 0.12% | 100.12% |
30-Nov-20 | PG_2 | PG2_G1 | SG1_1 | 0.77% | 100.77% |
30-Oct-20 | PG_2 | PG2_G1 | SG1_1 | 0.02% | 100.02% |
30-Sep-20 | PG_2 | PG2_G1 | SG1_1 | -0.20% | 99.80% |
31-Aug-20 | PG_2 | PG2_G1 | SG1_1 | -0.15% | 99.85% |
31-Jul-20 | PG_2 | PG2_G1 | SG1_1 | -0.39% | 99.61% |
30-Jun-20 | PG_2 | PG2_G1 | SG1_1 | -0.21% | 99.79% |
29-May-20 | PG_2 | PG2_G1 | SG1_1 | -0.71% | 99.29% |
30-Apr-20 | PG_2 | PG2_G1 | SG1_1 | 0.20% | 100.20% |
31-Mar-20 | PG_2 | PG2_G1 | SG1_1 | -0.50% | 99.50% |
28-Feb-20 | PG_2 | PG2_G1 | SG1_1 | 0.84% | 100.84% |
31-Jan-20 | PG_2 | PG2_G1 | SG1_1 | 0.14% | 100.14% |
Measure Used
CumProd = PRODUCTX( TableFR, TableFR[ReturnsNorm] ) - 1
Appreciate any help, thanks.
Solved! Go to Solution.
@Wendeley-North , Try like
CumProd = var _1= PRODUCTX( TableFR, TableFR[ReturnsNorm] ) return CALCULATE(if(not(ISBLANK(_1)) , _1-1, blank()))
@Wendeley-North , Try like
CumProd = var _1= PRODUCTX( TableFR, TableFR[ReturnsNorm] ) return CALCULATE(if(not(ISBLANK(_1)) , _1-1, blank()))
Hi, that does solve the issue though not the slicer behaviour... will accept it. Many thanks.
Final code used
Period_GeoMean_Ret =
VAR result = PRODUCTX ( TableFR, TableFR[ReturnsNorm] )
RETURN
IF ( ISBLANK(result), BLANK(), result - 1 )
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |