Forum Discussion

MeiYing's avatar
MeiYing
Icon for Helper I rankHelper I
1 year ago
Solved

Cross filtering between weekly chart and quarterly chart

Hi all, 

 

I got weekly and quarterly bar charts to display total number of orders. Let say there are total order for week 10 is 100 and its respective quarter 1 got 2000. When I select week 10, it will also cross filter the quarterly chart but it filters to show total order of the week which is 100. What I want is, when a week is filtered, quarterly chart will filter to its respective quarter, which is quarter 1, but still display 2000, not 100. Does anyone know if there's any way to achieve that? 

 

Thank you!

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi MeiYing ,

     

    I think currently, you are using one active relationship like [Date] between Data Table and your Date Table.

    So when you select week10, the visual show quarter data will only show data in week10, due to relationship.

    Here I suggest you to try data model as below. Add [WeekNum] and [Quarter] column in your data table and then create two inactive relationship between two tables.

    Measure:

    Weekly = CALCULATE(SUM('Table'[Sales]),USERELATIONSHIP('Calendar'[WeekNum],'Table'[WeekNum]))
    Quarterly = CALCULATE(SUM('Table'[Sales]),USERELATIONSHIP('Calendar'[Quarter],'Table'[Quarter]))

    Result is as below.

     

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi MeiYing ,

     

    I think currently, you are using one active relationship like [Date] between Data Table and your Date Table.

    So when you select week10, the visual show quarter data will only show data in week10, due to relationship.

    Here I suggest you to try data model as below. Add [WeekNum] and [Quarter] column in your data table and then create two inactive relationship between two tables.

    Measure:

    Weekly = CALCULATE(SUM('Table'[Sales]),USERELATIONSHIP('Calendar'[WeekNum],'Table'[WeekNum]))
    Quarterly = CALCULATE(SUM('Table'[Sales]),USERELATIONSHIP('Calendar'[Quarter],'Table'[Quarter]))

    Result is as below.

     

    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.