Display related number of weeks in weekly chart according to filter in quarterly chart

[MeiYing]

Hi 

I have quarterly and weekly bar charts on dashboard. I want to build a dashboard where I can select any quarter and 13 weeks of data from that particular quarter will be displayed in weekly chart. And it should display the last 13 weeks of data by default when there is no selection. Can anyone advise me how to do that? 

Thanks in advance. 

This is my current query. 

select * from table where trunc(ORDERDATE) between trunc(sysdate - to_char(sysdate, 'd') - 91) and trunc(sysdate) order by ORDERDATE"

[v-jianboli-msft · Accepted solution]

Hi @MeiYing ,

Based on your description, I have created a simple sample:

Please try to add this measure to the visual's filter:

Flag = 
var _a = CALCULATE(MAX('Table'[YearQuarter Number]),ALL('Table'))
var _b = SELECTCOLUMNS(FILTER(ALL('Table'),[YearQuarter Number] = _a ),"Week",[Week])
return 
SWITCH(TRUE(),
ISFILTERED('Table'[Quarter]),1,
NOT(ISFILTERED('Table'[Quarter]))&&SELECTEDVALUE('Table'[Week]) in _b,1)

Final output:

Best Regards,

Jianbo Li

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

[MeiYing]

Hi Jianbo, 

Thanks a lot!! Your post really helps me. 

Can I ask one more thing? 

In my data, the last quarter still does not have total 13 weeks and so, when the quarter is not filtered, I want the weekly chart to always display the last 13 weeks (top 13 weeks). 

I added some variables to your Flag but it returns me all the weeks. I think there is something wrong my DAX.

Could you please help to check?

Flag =

var a = CALCULATE(MAX(CarrierOTS_KPI_Quarter[YearQuarterNumber]), ALL(CarrierOTS_KPI_Quarter))

var b = SELECTCOLUMNS(FILTER(ALL(CarrierOTS_KPI_Quarter),CarrierOTS_KPI_Quarter[YearQuarterNumber] = a),"W",CarrierOTS_KPI_Quarter[WEEK])

VAR c = TOPN(13, CarrierOTS_KPI_Quarter, CarrierOTS_KPI_Quarter[YearWeekNumber], DESC)

VAR d = SELECTCOLUMNS(c, "M", CarrierOTS_KPI_Quarter[WEEK])

return

SWITCH(TRUE(),

ISFILTERED(CarrierOTS_KPI_Quarter[QTR]),1,

NOT(ISFILTERED(CarrierOTS_KPI_Quarter[QTR]))&&SELECTEDVALUE(CarrierOTS_KPI_Quarter[WEEK]) in d,1)

[v-jianboli-msft]

Hi @MeiYing ,

Please check if this could work:

Flag =
VAR c = TOPN(13, ALL(CarrierOTS_KPI_Quarter), CarrierOTS_KPI_Quarter[YearWeekNumber], DESC)
VAR d = SELECTCOLUMNS(c, "M", CarrierOTS_KPI_Quarter[WEEK])
return
SWITCH(TRUE(),
ISFILTERED(CarrierOTS_KPI_Quarter[QTR]),1,
NOT(ISFILTERED(CarrierOTS_KPI_Quarter[QTR]))&&SELECTEDVALUE(CarrierOTS_KPI_Quarter[WEEK]) in d,1)

If you have any further questions, please feel free to let me know.

Best Regards,

Jianbo Li

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

[MeiYing]

Hi Jianbo,

Thank you so much for your quick response. I tried it and it only returns the last 1 week.

[v-jianboli-msft]

Hi @MeiYing ,

Sorry, I can't reproduce your issue.

Could you please share me with your pbix file after removing sensitive data?

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi @MeiYing ,

Based on your description, I have created a simple sample:

Please try to add this measure to the visual's filter:

Flag = 
var _a = CALCULATE(MAX('Table'[YearQuarter Number]),ALL('Table'))
var _b = SELECTCOLUMNS(FILTER(ALL('Table'),[YearQuarter Number] = _a ),"Week",[Week])
return 
SWITCH(TRUE(),
ISFILTERED('Table'[Quarter]),1,
NOT(ISFILTERED('Table'[Quarter]))&&SELECTEDVALUE('Table'[Week]) in _b,1)

Final output:

Best Regards,

Jianbo Li

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

[MeiYing]

Hi Jianbo, 

Thank you so much for the sample. Can I ask what is the purpose of 'Value' column and can you help to explain how Flag measure works? 

[v-jianboli-msft · Accepted solution]

 Hi @MeiYing ,

'Value' is just the value in my sample, used for display in visual, it has no purpose. You do not have to think about it.

Here is the annotation of this Measure:

Flag =
VAR _a =
    CALCULATE ( MAX ( 'Table'[YearQuarter Number] ), ALL ( 'Table' ) ) // Get the max YearQuarter Number from your table, ignoring any filters that have been applied
VAR _b =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Table' ), [YearQuarter Number] = _a ),
        "Week", [Week]
    ) // Get the last 13 weeks by using the var _a
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Table'[Quarter] ), 1,
        // if the slicer is selected, just display the filtered data
        NOT ( ISFILTERED ( 'Table'[Quarter] ) )
            && SELECTEDVALUE ( 'Table'[Week] ) IN _b, 1
    )
// if the slicer is not selected, display the data that in var _b

Best Regards,

Jianbo Li

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