Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hey,
I would like to ask you for help with a filter, I have a dashboard where are 4 charts - it is a weekly report, one chart shows data for the last 12 weeks, 2 charts for last 4 weeks and one is for the current week.
Is it possible to create 1 filter for all of them (ideally for the whole set) where the current week could be selected and the other charts would subtract the weeks? Or is there a solution that would be simple to filter such charts?
Thank you
Solved! Go to Solution.
Hi @Barculez ,
I created some data:
Here are the steps you can follow:
1. Create calculated column.
Slicer =
IF(
WEEKNUM('Table'[Date],2) = WEEKNUM(TODAY(),2),"Current Week","W"&""&WEEKNUM('Table'[Date],2))
2. Create calculated table.
Slicer_Table =
DISTINCT(
'Table'[Slicer])
3. Create measure.
Flag_current =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
IF(
_select="Current Week" ,
SUMX(
FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)= WEEKNUM(TODAY(),2)),[rand]),
SUMX( FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)=VALUE(RIGHT(_select,LEN(_select)-1))),[rand]))
Flag_4 =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
IF(
_select="Current Week" ,
SUMX(
FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)= WEEKNUM(TODAY(),2)-4),[rand]),
SUMX( FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)=VALUE(RIGHT(_select,LEN(_select)-1))-4),[rand]))
Flag_12 =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
IF(
_select="Current Week" ,
SUMX(
FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)= WEEKNUM(TODAY(),2)-12),[rand]),
SUMX( FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)=VALUE(RIGHT(_select,LEN(_select)-1))-12),[rand]))
4. Result:
Select Current Week:
Select W17:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Barculez ,
I created some data:
Here are the steps you can follow:
1. Create calculated column.
Slicer =
IF(
WEEKNUM('Table'[Date],2) = WEEKNUM(TODAY(),2),"Current Week","W"&""&WEEKNUM('Table'[Date],2))
2. Create calculated table.
Slicer_Table =
DISTINCT(
'Table'[Slicer])
3. Create measure.
Flag_current =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
IF(
_select="Current Week" ,
SUMX(
FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)= WEEKNUM(TODAY(),2)),[rand]),
SUMX( FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)=VALUE(RIGHT(_select,LEN(_select)-1))),[rand]))
Flag_4 =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
IF(
_select="Current Week" ,
SUMX(
FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)= WEEKNUM(TODAY(),2)-4),[rand]),
SUMX( FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)=VALUE(RIGHT(_select,LEN(_select)-1))-4),[rand]))
Flag_12 =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
IF(
_select="Current Week" ,
SUMX(
FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)= WEEKNUM(TODAY(),2)-12),[rand]),
SUMX( FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)=VALUE(RIGHT(_select,LEN(_select)-1))-12),[rand]))
4. Result:
Select Current Week:
Select W17:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |