Check if values in a table created by SELECTCOLUMNS exists in another table created by SELECTCOLUMNS

[RYU209]

Hello,

I've created a measure that uses two SELECTCOLUMNS() functions to create two virtual tables with one column a piece. 

CHECK = VAR Table1 = SELECTCOLUMNS(FILTER('Base Table', 'Base Table'[Condition] = "1"), "1 Column", 'Base Table'[Transaction])

VAR Table2 = SELECTCOLUMNS(FILTER('Base Table', 'Base Table'[Condition] = "2"), "2 Column", 'Base Table'[Transaction])

RETURN
IF(Table1 IN Table2, "A exists in B", "A is not in B") 

This isn't working as expect and I think it's because of the IN operator, or it could be because I am using Direct Query and Table2 brings in over 1M rows exceeding the limit. Can anyone help here? 

[RYU209 · Accepted solution]

Hi @tamerj1 ,

I'll try and create a mock-up of what my data looks like without sharing sensitive data. Thank you again for helping with this.

Both the Transaction and Material column can have duplicates and are not unique. Each Transaction and Material combination can also have a condition of 1, 2, or both. 

[tamerj1]

Hi @RYU209 

please try

RETURN
IF ( ISEMPTY ( EXCEPT ( Table1, Table2 ) ), "A exists in B", "A is not in B") 

this will check if all values of A exist in B

If you want to check if at least one value of A exists in B then use

RETURN
IF ( NOT ISEMPTY ( INTERSECT ( Table1, Table2 ) ), "A exists in B", "A is not in B") 

in case it is only one value then both expressions will produce the same result

[RYU209]

Hi @tamerj1 ,

My main goal is to go row by row in Table1 and check if that row exists in Table2. Then I plan on using this measure as a visual filter to restrict a Table visual to only show Transactions that exist in both Table1 and Table2. So it's not an all or nothing check but a row by row check. Would I need to restructure my approach here? 

[tamerj1]

@RYU209 

The values in you table visual are aggregated at which level?

[RYU209]

@tamerj1 I'm not sure what that means. 

[tamerj1]

@RYU209 

I mean there is nothing wrong with this approach but I can see a contradiction between the approach and your description of the table visual. You have stated that you need to limit the transactions displayed by the visual which means the table visual displays the data at row level (no aggregation). In other words 1 row in the table visual = 1 row in the source data. While the approach suggests multiple rows in the filter context that are being filtered based on different conditions. I could go further and start proposing solutions based on my above understanding of the problem but perhaps I'm mistaken. Therefore, please provide more information like sample data and/or a screenshot of tge visual for better understanding of the problem. 

[RYU209]

@tamerj

Correct, the table visual will behave as described. Please feel free to correct my approach as I am not super familiar with SELECTCOLUMNS(). 

[tamerj1]

@RYU209 
Please try

=
VAR T =
    CALCULATETABLE (
        VALUES ( 'Base Table'[Transaction] ),
        'Base Table'[Condition] = "2",
        ALLSELECTED ( 'Base Table' )
    )
RETURN
    COUNTROWS (
        FILTER (
            'Base Table',
            'Base Table'[Condition] = "1"
                && 'Base Table'[Transaction] IN T
        )
    )

[RYU209]

@tamerj1 

Thank you for the detailed response! I didn't mention in the original question that the measure filter also needs to restrict Transactions based off a [Material] column. The Base Table can have duplicate Transactions each with a different Material and each row in the data set can also have a [Condition] of 1 or 2. I need to find a way to retrieve Transactions that have the same Transaction ID, Material, and have a condition of both 1 and 2. The table (visual) then needs to be restricted to only show Transactions that fall under this condition but ONLY show the transactions with [Condition] = 2. 

Sorry if this isn't clear! 

[tamerj1]

@RYU209 
Alright. It dose not seem to be complex. The only complex thing for me is that I have no idea how your data looks like. PLease share a sample or a screenshot for bettwer understanding of the problem.

Is the transaction ID a primary (unique) key in the table? If not then is material ID  a primary (unique) key in the table?

Please advise.

[RYU209]

This is what the data looks like. There are other data elements but they are not relevant to how the Table (Visual) needs to be filtered.

The Table in Red denotes how the Visual should look like after filtering. The data on the left is how the raw data looks like.

[tamerj1 · Accepted solution]

@RYU209 

Please try

=
VAR CurrentCondition =
SELECTEDVALUE ( 'Base Table'[Condition] )
VAR T =
CALCULATETABLE (
VALUES ( 'Base Table'[Condition] ),
ALLEXCEPT ( 'Base Table', 'Base Table'[Transaction], 'Base Table'[Material] )
)
RETURN
IF ( COUNTROWS ( T ) = 2 && CurrentCondition = "2", 1 )

[RYU209 · Accepted solution]

Hi @tamerj1 ,

I'll try and create a mock-up of what my data looks like without sharing sensitive data. Thank you again for helping with this.

Both the Transaction and Material column can have duplicates and are not unique. Each Transaction and Material combination can also have a condition of 1, 2, or both.