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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MeiYing
Helper I
Helper I

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

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. 

 

MeiYing_0-1685943276602.png

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"

2 ACCEPTED SOLUTIONS
v-jianboli-msft
Community Support
Community Support

Hi @MeiYing ,

 

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

vjianbolimsft_0-1686115943263.png

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:

vjianbolimsft_1-1686116009642.png

vjianbolimsft_2-1686116022802.png

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.

View solution in original post

 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.

View solution in original post

7 REPLIES 7
MeiYing
Helper I
Helper I

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)

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.

Hi Jianbo,

 

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

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
Community Support
Community Support

Hi @MeiYing ,

 

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

vjianbolimsft_0-1686115943263.png

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:

vjianbolimsft_1-1686116009642.png

vjianbolimsft_2-1686116022802.png

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.

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? 

 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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.