Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 )
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |