Compare single outcome in many to many relation

[BeeleJa]

Within 2 separate not linked tables I am looking for a value from my 1st table that returns multiple values from the second table and from the  multiple valuse in the second table is needs to chech if the value is present in anywhere another field in the 1st table

Output either a 0 or 1 or the value from table 2 on the value of the first used field in the 1st table

Does anybody have a possible solution for this?

[v-kkf-msft · Accepted solution]

Hi @BeeleJa ,

You can achieve it in Power Query Editor.

1. Matching WOMAN column to merge Table 1 and Table 1.

2. Expand the ItemID_Standard column in this merged table.

3. Matching ITEMID and Change Type.ItemID_Standard columns to merge Table 1 and Table 2.

4. Expand the aggregated values of the ALTERNATIVEITEMID column.

5. Sum the aggregated values of the previous step group by columns WOMAIN, ITEMID, ItemID_Standard and PRODUCTNAME.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[BeeleJa]

Dear Winniz,

How would the process be if I would like to show the AlternativeItemID from the Table2 instead of a number?

Would the solution be more easy / simplified?

Regards,

[BeeleJa]

Dear Ibendlin,

Thank you for you response.

I am not familiar with DAX Studio and do not know if I have this as a standard available. From the above solution I only notice that art the first merge the number of records grows extremly large and that continues when merging further.

regards

[lbendlin]

you can achieve that with pointer math, either with measures or with calculated columns. Indicate which one you want. Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) and show the expected outcome.

[BeeleJa]

Calculated columns has the preference.

I have a PBIX file available to work with, how can I share this file with you?

[BeeleJa]

Table 1 

WOMAIN	ITEMID	ItemID_Standard	PRODUCTNAME	Required Result	Remark
W0000345	7001576B_ER01	7001576B	Housing Assy, Exhaust-Turbine	0
W0000345	7006653	7006653	Housing Assy, Exhaust-Turbine	1
W0001500	7004477A	7004477A	Housing Assy, Exhaust-Turbine	1
W0001500	7006653_IRE01	7006653	Housing Assy, Exhaust-Turbine	0
W0003199	7001576B	7001576B	Housing Assy, Exhaust-Turbine	1
W0003199	7006653_ER02	7006653	Housing Assy, Exhaust-Turbine	0
W0003376	7004477A_ER02	7004477A	Housing Assy, Exhaust-Turbine	0
W0003376	7006653	7006653	Housing Assy, Exhaust-Turbine	1
W0004034	7004477A_ER02	7004477A	Housing Assy, Exhaust-Turbine	0
W0004034	7004477A_IRE01	7004477A	Housing Assy, Exhaust-Turbine	0
W0004034	7006653	7006653	Housing Assy, Exhaust-Turbine	2
W0006411	7004477	7004477	Housing Assy, Exhaust-Turbine	1
W0006411	7006653_ER01	7006653	Housing Assy, Exhaust-Turbine	0
For the same values in A:A - from the value in cell B3 - to check if any value of C:C is present in the list where B3 = Table 2 (any value in A:A) and Any Value of C:C is present in Table 2 B:B

[BeeleJa]

Table 2

ITEMID	ALTERNATIVEITEMID
7001576B	7004477
7001576B	7004477A
7001576B	7006653
7004477	7001576B
7004477	7004477A
7004477	7006653
7004477A	7001576B
7004477A	7004477
7004477A	7006653
7006653	7001576B
7006653	7004477
7006653	7004477A

[v-kkf-msft · Accepted solution]

Hi @BeeleJa ,

You can achieve it in Power Query Editor.

1. Matching WOMAN column to merge Table 1 and Table 1.

2. Expand the ItemID_Standard column in this merged table.

3. Matching ITEMID and Change Type.ItemID_Standard columns to merge Table 1 and Table 2.

4. Expand the aggregated values of the ALTERNATIVEITEMID column.

5. Sum the aggregated values of the previous step group by columns WOMAIN, ITEMID, ItemID_Standard and PRODUCTNAME.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[BeeleJa]

Dear v-kkf-msft, 

Thank you for your sugestion however the data set i am working with is containing ore information than shgared / provided. Within you provided solution it leads to a process that has not reached the end after several hours.

Although the solution is marked as solved it cenrtainly has not been marked as such by me.

is there any opther solution possible?

[v-kkf-msft · Accepted solution]

Hi @BeeleJa ,

Please try another way.

• Add the Index column:

• Create the relationship:

• Create column:

Column = 
VAR tab1 =
    CALCULATETABLE (
        SUMMARIZE ( 'Table 1B', 'Table 1B'[Index], 'Table 1B'[ItemID_Standard] ),
        FILTER (
            ALL ( 'Table 1B' ),
            'Table 1B'[WOMAIN] = EARLIER ( 'Table 1B'[WOMAIN] )
        )
    )
VAR tab2 =
    CROSSJOIN (
        CALCULATETABLE (
            VALUES ( 'Table 1B'[Index] ),
            FILTER (
                ALL ( 'Table 1B' ),
                'Table 1B'[WOMAIN] = EARLIER ( 'Table 1B'[WOMAIN] )
            )
        ),
        CALCULATETABLE (
            VALUES ( 'Table 2'[ALTERNATIVEITEMID] ),
            FILTER ( 'Table 2', 'Table 1B'[ITEMID] IN VALUES ( 'Table 2'[ITEMID] ) )
        )
    )
RETURN
    IF (
        'Table 1B'[ITEMID] IN VALUES ( 'Table 2'[ITEMID] ),
        COUNTROWS ( DISTINCT ( INTERSECT ( tab1, tab2 ) ) ),
        0
    )

Best Regards,
Winniz

[BeeleJa]

Dear Winniz,

Thank you for the provided alternative solution - this is working for me.

Regards

[lbendlin]

Run the Query through DAX Studio and see where it fails. Most likely a high cardinality issue since you use disconnected tables.