Forum Discussion

fsadasdasd's avatar
fsadasdasd
Regular Visitor
2 years ago
Solved

Conditional filtering of a table based on x-axis date

Hello,

I have two tables :

Calendar (generated table for dates) 

 - Each line represents a day

 

Avis 

- Connected to calendar date with 2 inactives relationship : VIQMEL_QMDAT (open on)  and VIQMEL_QMDAB (closed on)

 

 

I've created 2 simple measures that I display in a plot with x-axis being the calendar date. The orange measure is not important there I guess.

- open : number of open "avis" 

 

count_open = CALCULATE(COUNTROWS(FILTER('avis', 'avis'[VIQMEL_QMDAT])),USERELATIONSHIP('calendar'[Date],avis[VIQMEL_QMDAT]))

 

- closed : number of closed "avis"

 

count_closed = CALCULATE(COUNTROWS    (
                                FILTER ('avis', 'avis'[VIQMEL_QMDAB] <> BLANK())
                            ),USERELATIONSHIP('calendar'[Date],avis[VIQMEL_QMDAB]))

 

 

My concern now is that I'd like the table below the chart (which displays random columns from the "avis" table) to be filtered when I click on a specific date in the chart. For example, if I click on January 1, 2023, the table should only display rows that have either VIQMEL_QMDAT == "2023.01.01" or VIQMEL_QMDAB == "2023.01.01"

 

We could also imagine having 2 different tables for these two cases.

 

Any ideas on how I could manage this?


Thank's a lot!


 

  • Hi fsadasdasd,

     

    For this you need to create a second measure where you simply do:

    Filter = IF([VIQMEL_QMDAT] <> BLANK() || [VIQMEL_QMDAB] <> BLANK(), 1)

     

    Now use this measure as a filter on your table as not blank:

     

     

3 Replies

  • Hi fsadasdasd,

     

    For this you need to create a second measure where you simply do:

    Filter = IF([VIQMEL_QMDAT] <> BLANK() || [VIQMEL_QMDAB] <> BLANK(), 1)

     

    Now use this measure as a filter on your table as not blank:

     

     

    • fsadasdasd's avatar
      fsadasdasd
      Regular Visitor

      Hey, 

      Thank's for the answer! I did a small modification by using the measures themselves instead of the columns and it works. 

      filter_table = IF([count_open] <> BLANK() || [count_closed] <> BLANK(), 1)

       

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User

        Hi meant to use the measures and not the columns, sorry for the incorrection but glad you pick it up.