Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
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"
)
Hope that solves it for your case!
Best regards,
Tim
Proud to be a Super User!
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 , 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]))
Thanks @amitchandak
But isn't this calculating the COUNT?
I want to filter a table visual using these conditions.
Looking forward to your solution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |