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

[JSIQUEI-YYC-ENB]

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!

[Anonymous · Accepted solution]

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.

[JSIQUEI-YYC-ENB]

@Anonymous Thanks a lot... that's helped! 

[lbendlin]

You cannot create calculated tables from measures.  Instead, you need to use the measure as a visual filter.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...