Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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.

timg
Solution Sage
Solution Sage

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"
)

ExampleExample 

Hope that solves it for your case!

 

Best regards,

Tim





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

amitchandak
Super User
Super User

@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
Not applicable

Thanks @amitchandak

 

But isn't this calculating the COUNT?

I want to filter a table visual using these conditions.

 

Looking forward to your solution.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.