topic Re: How to filter table with value related to the selectedvalue in DAX Commands and Tips

How to filter table with value related to the selectedvalue

char23 — Fri, 09 Aug 2024 22:45:10 GMT

I have the two tables below in my power bi file. I have a slicer from Table2 that uses column [Month] as the selected value. If a month is selected, I want to calculate and filter Table1 where Table1 = the associated selected [Date Rank] from Table2 + 1. For example, if 24-Jun is selected from Table2, I want to filter Table1 where [Month] = 24-May.

Table1

ID	Status	Date Rank	Previous Status	Month	Type	Group
A	Normal	3	No previous	24-Mar	Task	<0
B	Normal	3	No previous	24-Mar	Task	1 to 5
C	Not Normal	3	No previous	24-Mar	Task	1 to 5
D	Not Normal	3	No Previous	24-Mar	Not Task	11 to 15
A	Not Normal	2	Normal	24-May	Task	6 to 10
B	Normal	2	Normal	24-May	Not Task	6 to 10
C	Normal	2	Not Normal	24-May	Not Task	6 to 10
D	Not Normal	2	Not Normal	24-May	Task	6 to 10
A	Not Normal	1	Not Normal	24-Jun	Task	1 to 5
B	Not Normal	1	Normal	24-Jun	Task	<0
D	Normal	1	Normal	24-Jun	Task	<0
E	Normal	1		24-Jun	Task	1 to 5

Table2

Date Rank	Month
3	24-Mar
2	24-May
1	24-Jun

Re: How to filter table with value related to the selectedvalue

lbendlin — Sat, 10 Aug 2024 00:03:11 GMT

Add another column to Table2, call it "Next Date Rank", and set it to [Date]+1. Use that column to join to Table1.

Re: How to filter table with value related to the selectedvalue

Anonymous — Mon, 12 Aug 2024 02:37:06 GMT

Hi, char23

Thanks for lbendlin‘s reply. You can put this measure in filter pane and set to 1 to filter data.

filter = VAR _month = SELECTEDVALUE ( 'Table 2'[Month] ) VAR _dateRank = CALCULATE ( MAXX ( 'Table 2', 'Table 2'[Date Rank] ) + 1, FILTER ( 'Table', 'Table'[Month] = _month ) ) VAR _result = IF ( SELECTEDVALUE ( 'Table'[Date Rank] ) = _dateRank, 1 ) RETURN _result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: How to filter table with value related to the selectedvalue

char23 — Mon, 12 Aug 2024 15:56:18 GMT

Thank you for the suggestion. Can you elaborate. I tried this method and still haveing trouble getting it to work. This is what I have below, but it just returns blank.

VAR SelectedNextDateRank =

CALCULATE(

MAX('Table2'[Next Date Rank]),

'Table2'[Month] = SELECTEDVALUE('Table2'[Month])

)

RETURN

COUNTROWS(

CALCULATETABLE(

'Table1',

'Table1'[Date Rank] = SelectedNextDateRank

)

Re: How to filter table with value related to the selectedvalue

char23 — Mon, 12 Aug 2024 16:02:49 GMT

Thank you, I'm not sure if this would work for my visual since I have other measures on the same visual. Is there anyway to write this where the filter for 1 is in the measure itself? I am trying to understand how your formula works.

Re: How to filter table with value related to the selectedvalue

Anonymous — Mon, 26 Aug 2024 09:00:52 GMT

Hi, char23

The logic of this formula is to find the corresponding Date Rank of Table 2 according to the selected date and add 1. If the Date Rank of Table 1 is the same as the corresponding Date Rank of Table 2, then the Flag will be 1, otherwise, it will be 0. Add this Measure to the Filter box of the visual object, and set it to 1, then it will display all rows that have a filter of 1. What are the main problems you are currently facing that you can mark with a picture.

Best Regards,
Yang
Community Support Team

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum