Forum Discussion

JSIQUEI-YYC-ENB's avatar
JSIQUEI-YYC-ENB
Helper I
2 years ago
Solved

Filtering on Column in a Calculated Table by Others Column in distinct Tables

Hi all, need a big help on this request. 

1- I have a table called 'Transactions Table' that needs to be filter by 'Table Filter 1' and 'Table Filter2'

2- I need the Order Numbers on 'Table Filter 1' and 'Table Filter 2' being removed from 'Transactions Table'

3- I have a calculated table called 'Transactions Table Filtered' where I was able to remove the orders number from 'Table Filter 1' and ' Table Filter 2' (see measure below)

4- My problem is, the Calculated Table must change when I change the 'Report Date' slicer. That's mean, if I select report date as 31-Dec-2023 from the Report Date Table, I want to filter only the Order Numbers from 'Table Filter 1' and 'Table filter 2' with the same correspondent Report Date. 

here's my sample tables for a better undestanding:

 

and my Calculated Table measure which is missing the Filter component:

 

any help will be really appreciated!

thanks!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi JSIQUEI-YYC-ENB ,

     

    My Sample:

    Please try code as below to create a meausre. Then you can add this measure into visual level filter and set it to show items when value = 1.

    M1 = 
VAR _Table1 =
    VALUES ( 'Table Filter 1'[Order Number] )
VAR _Table2 =
    VALUES ( 'Table Filter 2'[Order Number] )
RETURN
    IF (
        OR (
            MAX ( 'Transactions Table'[Order Number] ) IN _Table1,
            MAX ( 'Transactions Table'[Order Number] ) IN _Table2
        ),
        0,
        1
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies