Forum Discussion
Helper IICalculation Based on Selected Value
Thanks Anonymous.
I ended up doing a workaround that uses a chiclet slicer, but what you provided would likely work. What I did was use a normal slicer for the week then added a chiclet slicer for the epoch. The chiclet slicer filters when a week is selected AND the value it filters to is a forced selection. This forced selection is the key to why the workaround works. I then turned off the interaction between my charts/tables and the week filter, and kept it on for the chiclet slicer.
The users don't need to see the chiclet slicer, so I hid it behind one of the charts. From their perspective, it looks like the week they're selecting is changing the values, which is what I wanted.
One other thing, the "Slicer (new)" option native to PBI has an option to force selection, but I could not get it to work. The chiclet slicer works just fine, though.
Thanks!
Hi jdwalker5 ,
1. Create a computed table to be used as a slicer and do not create a relationship with the original table.
Table =
DISTINCT('DATA'[Week to be selected])2. If you are trying to display the values of the item for the first 4 weeks of the selected week, then you can modify the expression as follows.
ROLLING_4_WK_LOS =
VAR BEGIN_WK =
LOOKUPVALUE(
'DATA'[Week Epoch],
'DATA'[Week to be Selected],
SELECTEDVALUE(
'Table'[Week to be selected])
) -3
VAR END_WK =
LOOKUPVALUE(
SELECTEDVALUE(
'Table'[Week to be selected])
)
VAR CALC =
CALCULATE(
SUM(
'DATA'[Count of item]), 'DATA'[Week Epoch] >= BEGIN_WK && 'DATA'[Week Epoch])
'DATA'[Week Epoch] >= BEGIN_WK && 'DATA'[Week Epoch] <= END_WK
)
RETURN CALC
3. If you are trying to ask for the sum of the items in the first four weeks of the selected week, change the fields in the table to fields in the calculation table.
Your calculations do not match the expected results because the slicer is the same field as the visual object to be displayed, and the slicer is what affects its visualization. For more detailed information, refer to the documentation: Slicers in Power BI - Power BI | Microsoft Learn.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IIThanks Anonymous.
I ended up doing a workaround that uses a chiclet slicer, but what you provided would likely work. What I did was use a normal slicer for the week then added a chiclet slicer for the epoch. The chiclet slicer filters when a week is selected AND the value it filters to is a forced selection. This forced selection is the key to why the workaround works. I then turned off the interaction between my charts/tables and the week filter, and kept it on for the chiclet slicer.
The users don't need to see the chiclet slicer, so I hid it behind one of the charts. From their perspective, it looks like the week they're selecting is changing the values, which is what I wanted.
One other thing, the "Slicer (new)" option native to PBI has an option to force selection, but I could not get it to work. The chiclet slicer works just fine, though.
Thanks!