Forum Discussion

Krystian514W's avatar
Krystian514W
Frequent Visitor
1 year ago
Solved

Relationship bypass problem

Hello!

 

I have a problem with showing a certain thing in the report, I will describe it with a maximally simplified example.

What I have:

first table with dates and names, second table for calendar - relationship between them by 'Date' columns.

 

It's obvious that the slicer (by week) and table/card (with names from Table) will show only few names, like below.

What can I do if I want to show two tables/cards below, one for filtered week and second one for next week? What I want to achieve:

Is it possible without duplicating tables? (I can copy my Table and move Date by a week, create another relationship... but my actual report is really big and this is not an option).

I tried to use selectedvalue(Week) for ALL(Table) but it returns BLANK because (if I understand correctly) I remove filter context, I think that maybe I can create two calendars and synchronize two different slicers, but I don't know how to do that. Maybe you can help me with that 🙂

 

Simple report in that link: Simple report 

Thanks!

 

Need Help
  • dk_dk's avatar
    dk_dk
    1 year ago

    Hi Krystian514W 

    Create a new measure:

    FilterCond =

    VAR weekn = WEEKNUM(CALCULATE(MAX(TBL[Date]),REMOVEFILTERS('Calendar')))

    VAR nextweek = MIN('Calendar'[Week])+1

    RETURN
    IF(weekn==nextweek,1,0)


    and add a visual level filter to the next week visual, and set FilterCond = 1.
     
    See my example:

     

     

     

     

     

    Unfortunately it stops working if I FilterCond is not explicitly part of the visual, but you can work around that by formatting the color of it to match the background, so it is invisible.

    Hope this helps! Let me know if you have any questions.

     

1 Reply

  • dk_dk's avatar
    dk_dk
    Super User
    1 year ago

    Hi Krystian514W 

    Create a new measure:

    FilterCond =

    VAR weekn = WEEKNUM(CALCULATE(MAX(TBL[Date]),REMOVEFILTERS('Calendar')))

    VAR nextweek = MIN('Calendar'[Week])+1

    RETURN
    IF(weekn==nextweek,1,0)


    and add a visual level filter to the next week visual, and set FilterCond = 1.
     
    See my example:

     

     

     

     

     

    Unfortunately it stops working if I FilterCond is not explicitly part of the visual, but you can work around that by formatting the color of it to match the background, so it is invisible.

    Hope this helps! Let me know if you have any questions.