Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Apply filter on two columns of a table visualisation dynamically

Hi,

 

I have a table of data and I want to filter it based on two conditions (OR, not AND) dynamically. Basically, I want to show all the records related to the most last month (last month available in the data table) OR those that record status is "Open" (regardless of its old date).

Can someone help me do that please?

 

Example of data:

 

IDDateStatus
R02020/12/20Closed
R12020/12/13Closed
R22020/12/10Open
R32020/11/25Closed
R42020/11/10Open
R52020/10/19Closed
R62020/10/18Closed
R72020/10/15Open

 

In the above example, it has to show R0, R1, R2 (most last month of data), R4, and R7 (more "Open" records).

 

Note 1: I want the date filtering be dynamic. That is, if I limit the date slicer to end of November 2020 (not Dec 2020), it only shows R3, R4 as well as R7.

Note 2: I have a calendar table that Date is linked to that.

 

Looking forward to your kind helps.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi,

     

    I finally managed to do this task using a combination of the above two answers. The final solution is:

     

    Measure =
VAR _maxMonth = EOMONTH(MAXX(ALLSELECTED('Calendar'), 'Calendar'[Date]), 0)
RETURN
CALCULATE(COUNT('Table'[ID]), FILTER('Table',(EOMONTH('Table'[Date],0) == _maxMonth && 'Table'[Status] = "Closed") || 'Table'[Status] ="Open"))

     

     

    Then I used this measure as a filter on the table visual and selected "greater than 0".

     

    Thanks very much.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Prefer  to use a disconnected date table

    Try measure like

    Measure =
    var _max = maxx(allselected('Date'), 'Date'[Date])
    return
    calculate(count(table[ID]),filter(table,(table[date] > _max && [State] ="Closed") || [State] ="Open"))

     

    or

     

    Measure =
    var _max = maxx(allselected('Date'), 'Date'[Date])
    return
    calculate(count(table[ID]),filter(table,(table[date] > _max && [State] ="Closed") || [State] ="Open"), all(Date[Date]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks amitchandak

       

      But isn't this calculating the COUNT?

      I want to filter a table visual using these conditions.

       

      Looking forward to your solution.

  • timg's avatar
    timg
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi Mah,

    A calculated column could work here. You could use this as a slicer to filter only the max or all records

     

    Column = 
SWITCH (
    TRUE (),
    EOMONTH ( 'Table'[Date], 0 ) = EOMONTH ( MAXA ( 'Table'[Date] ), 0 )
        || 'Table'[Status] = "Open", "Max"
)

    Example 

    Hope that solves it for your case!

     

    Best regards,

    Tim

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks timg

       

      Unfortunately, this solution is not working perfectly. When using date as the slicer, it does not recognize the most last month in the date range as the last month. It just recognizes the last month in the total dataset as the last month. In other words, it is not working dynamically. However, it is working well with the open status ones.

       

      Looking forward to your help and amendment.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

     

    I finally managed to do this task using a combination of the above two answers. The final solution is:

     

    Measure =
VAR _maxMonth = EOMONTH(MAXX(ALLSELECTED('Calendar'), 'Calendar'[Date]), 0)
RETURN
CALCULATE(COUNT('Table'[ID]), FILTER('Table',(EOMONTH('Table'[Date],0) == _maxMonth && 'Table'[Status] = "Closed") || 'Table'[Status] ="Open"))

     

     

    Then I used this measure as a filter on the table visual and selected "greater than 0".

     

    Thanks very much.