cancel
Showing results for
Did you mean:
Resolver I

## 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:

 ID Date Status R0 2020/12/20 Closed R1 2020/12/13 Closed R2 2020/12/10 Open R3 2020/11/25 Closed R4 2020/11/10 Open R5 2020/10/19 Closed R6 2020/10/18 Closed R7 2020/10/15 Open

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.

1 ACCEPTED SOLUTION
Resolver I

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 5
Resolver I

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.

Solution Specialist

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

Resolver I

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.

Super User IV

@mah65 , 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]))

Proud to be a Super User!

Resolver I

Thanks @amitchandak

But isn't this calculating the COUNT?

I want to filter a table visual using these conditions.

Announcements