cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mah65
Resolver I
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:

 

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
mah65
Resolver I
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.

View solution in original post

5 REPLIES 5
mah65
Resolver I
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.

View solution in original post

timg
Solution Specialist
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"
)

ExampleExample 

Hope that solves it for your case!

 

Best regards,

Tim

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 IV
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]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.