Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jmdublu
Frequent Visitor

Recalculate Index From Slicer Selection and Use Result to Filter Visuals

Have searched extensively to try to solve this and finally decided I need to ask for help.  Will try to make this as simple as possible. 

 

I have a data source at weekly grain for current month to current month + 2.
Base table includes Week Start Date and Number of Weeks From Current Week (calculated in SQL) for each entity. 

 

Currently filtering 4 visuals where first visual filter is [Weeks From Current]  = 1, second visual is [Weeks From Current] = 2, etc. (dates change each new month; default view requested from business is next week and 3 following weeks).

 

Now I want the user to be able to select the Week Start Date from Slicer, and visuals will shift to the week selected, plus the next 3 weeks. 

 

I want to replace current filter with new filter on calculated week: first visual to [New Week Number] = 1, second visual to [New Week Number] = 2, etc. based on selection.

 

I want to avoid Time Intelligence if at all possible.  What I have done so far:

 

Created a calculated table (WEEK_BASE) for distinct Week Name and Weeks From Current and made this Slicer source.

Created second calculated table (WEEK_REBASE) from first with working Measures for:

a) Week Selected (either DAX version below works):

- IF(HASONEVALUE(WEEK_BASE[Weeks From Current]),VALUES(WEEK_BASE[Weeks From Current]), BLANK())
- SELECTEDVALUE(WEEK_BASE[Weeks From Current])

b) New Week Number:

- SUMX(WEEK_REBASE, WEEK_REBASE[Weeks From Current] - WEEK_REBASE[w_Selected_Week] + 1)

Created a third calculated table (WEEK_FILTER) from 2nd table and pulled measures as columns:

Herein lies the problem:

- [Week Selected] comes into the new table blank

- [New Week Number] comes into the new table as [Weeks From Current] + 1

* (Since Week Selected is blank it is treated as 0 and only the + 1 is calculated)

* Even table refresh does not update these values

 

Sample data (where Slicer selected value = 11-03-24):

Period NameWeeks from CurrentWeek SelectedNew Week Number
Week of: 09-01-24-27-8
Week of: 09-08-24-17-7
Week of: 09-15-2407-6
Week of: 09-22-2417-5
Week of: 09-29-2427-4
Week of: 10-06-2437-3
Week of: 10-13-2447-2
Week of: 10-20-2457-1
Week of: 10-27-24670
Week of: 11-03-24771
Week of: 11-10-24872
Week of: 11-17-24973
Week of: 11-24-241074

 

Screenshot:

Recalculate_Week_Index_and_Filter_with_New_Value.png

Desired result based on Slicer selection above would be Week of: 11-03-24 and Week of 11-10-24, etc.

 

Thank you in advance for any possible solution.

5 REPLIES 5
jmdublu
Frequent Visitor

Thank you very much for your response @v-rzhou-msft.  I will dig into this and see if I can apply same to my situation.

Hi @jmdublu ,

 

I hope my workaround could help you solve your issue. If it is, please kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your data model and your issue or share me with your pbix file without sensitive data.


Best Regards,

Rico Zhou

@v-rzhou-msft,
Please kindly review the updated .pbix file here and provide any additional feedback regarding the problem as I've outlined further in my most recent response.  Thank you again, very kindly for your assistance.

v-rzhou-msft
Community Support
Community Support

Hi @jmdublu,

 

Here I create a Calendar Table as below.

DimDate = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 09, 01 ), DATE ( 2024, 12, 31 ) ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "WeekStart",
        [Date] - WEEKDAY ( [Date], 1 ) + 1,
    "Period Name",
        "Week of:" & " "
            & FORMAT ( [Date] - WEEKDAY ( [Date], 1 ) + 1, "MM-DD-YY" )
)
Rank = RANKX(DimDate,[WeekStart],,ASC,Dense)
Week from Current = 
VAR _WeekStart = TODAY() - WEEKDAY(TODAY(),1)+1
RETURN
[Rank] - CALCULATE(MAX(DimDate[Rank]),FILTER(DimDate,DimDate[WeekStart] = _WeekStart))

A Calculated Table like yours:

Table = SUMMARIZE(DimDate,DimDate[Period Name],DimDate[Week from Current])

An unrelated DimPeriod Table:

DimPeriod = 
'Table'

If you want dynamic result, please try measures.

WeekSelected = SELECTEDVALUE(DimPeriod[Week from Current])
New Week Number = CALCULATE(SUM('Table'[Week from Current])) - [WeekSelected] + 1

Result is as below.

vrzhoumsft_0-1727246451128.png

vrzhoumsft_1-1727246469684.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Hello @v-rzhou-msft,

I've had a chance to try to put your solution in action and I've run into a problem.  The selection filters the matrix in your sample .pbix, but when I try to apply it to the visuals in my dashboard, I am experiencing the same problem as before- filtering with new week number does not produce expected results (though I can at least modify the filter when I add it to the visual now...).

In the example in the image below, the selection of 10-06-24 should result in Week "1" visual producing values for Week of 10-06-24 and Week "2" visual producing values for Week of 10-13-24, and so on for weeks 3 and 4.  Instead the selection has no effect.

Recalculate_Week_Index_and_Filter_with_New_Value2.png

I attempted to attach updated version of your .pbix, but I do not see the option to attach files anywhere in the interface. 

 

Here is the code I used to generate the sample data that is populating the "Data" table:

Data = 
VAR sites = DATATABLE(
    "Site", STRING,
    { 
        {"A"},{"B"},{"C"},{"D"},{"E"},{"F"},{"G"},{"H"}
    }
)

VAR tbl = 
ADDCOLUMNS(
    CROSSJOIN(SUMMARIZE(sites, [Site]), 
    SUMMARIZE(DimPeriod, DimPeriod[Period Name], DimPeriod[Week from Current])),
    "Fake Actual Volume", ROUND(RAND() * 140000,0)
)

VAR rslt = 
SUMMARIZE(tbl, 
    [Site], 
    [Period Name],
    [Week from Current],
    [Fake Actual Volume],
    "Fake Budget Volume", [Fake Actual Volume] - RANDBETWEEN([Fake Actual Volume] * -0.30, [Fake Actual Volume] * 0.30)
)

RETURN rslt

 

Since my data is at weekly grain, and does not include day dates, I modified DimDate so that I could link to Data as many:1:

NewDateDim = 
DISTINCT(
    SELECTCOLUMNS(DimDate,
    "Period Name", DimDate[Period Name],
    "Week Start", DimDate[WeekStart],
    "Rank", DimDate[Rank],
    "Week from Current", DimDate[Week from Current]
    )
)

 

I joined this table to Data on Period Name.  Hopefully this additional information helps clarify my need.

 

Thank you very much again for your help.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.