Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BeeleJa
Helper I
Helper I

Compare single outcome in many to many relation

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?

 

BeeleJa_0-1631271955934.png

 

2 ACCEPTED SOLUTIONS

Hi @BeeleJa ,

 

You can achieve it in Power Query Editor.


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

 

vkkfmsft_0-1631690566517.png

 

2. Expand the ItemID_Standard column in this merged table.

 

vkkfmsft_1-1631690633647.png

 

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

 

merge.png

 

4. Expand the aggregated values of the ALTERNATIVEITEMID column.

vkkfmsft_3-1631691624287.png

 

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

 

image.png

vkkfmsft_2-1631691380190.png

 

 

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.

View solution in original post

Hi @BeeleJa ,

 

Please try another way.

 

  • Add the Index column:

vkkfmsft_0-1632988976872.png

 

  • Create the relationship:

vkkfmsft_1-1632989085256.png

 

  • 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
    )

vkkfmsft_2-1632989191115.png

 

Best Regards,
Winniz

 

 

 

View solution in original post

11 REPLIES 11
BeeleJa
Helper I
Helper I

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
Helper I
Helper I

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
Super User
Super User

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.

Calculated columns has the preference.

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

 

 

Table 1 

WOMAINITEMIDItemID_StandardPRODUCTNAMERequired ResultRemark
W00003457001576B_ER017001576BHousing Assy, Exhaust-Turbine0 
W000034570066537006653Housing Assy, Exhaust-Turbine1 
W00015007004477A7004477AHousing Assy, Exhaust-Turbine1 
W00015007006653_IRE017006653Housing Assy, Exhaust-Turbine0 
W00031997001576B7001576BHousing Assy, Exhaust-Turbine1 
W00031997006653_ER027006653Housing Assy, Exhaust-Turbine0 
W00033767004477A_ER027004477AHousing Assy, Exhaust-Turbine0 
W000337670066537006653Housing Assy, Exhaust-Turbine1 
W00040347004477A_ER027004477AHousing Assy, Exhaust-Turbine0 
W00040347004477A_IRE017004477AHousing Assy, Exhaust-Turbine0 
W000403470066537006653Housing Assy, Exhaust-Turbine2 
W000641170044777004477Housing Assy, Exhaust-Turbine1 
W00064117006653_ER017006653Housing Assy, Exhaust-Turbine0 
      
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

Table 2

 

ITEMIDALTERNATIVEITEMID
7001576B7004477
7001576B7004477A
7001576B7006653
70044777001576B
70044777004477A
70044777006653
7004477A7001576B
7004477A7004477
7004477A7006653
70066537001576B
70066537004477
70066537004477A

Hi @BeeleJa ,

 

You can achieve it in Power Query Editor.


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

 

vkkfmsft_0-1631690566517.png

 

2. Expand the ItemID_Standard column in this merged table.

 

vkkfmsft_1-1631690633647.png

 

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

 

merge.png

 

4. Expand the aggregated values of the ALTERNATIVEITEMID column.

vkkfmsft_3-1631691624287.png

 

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

 

image.png

vkkfmsft_2-1631691380190.png

 

 

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.

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?

Hi @BeeleJa ,

 

Please try another way.

 

  • Add the Index column:

vkkfmsft_0-1632988976872.png

 

  • Create the relationship:

vkkfmsft_1-1632989085256.png

 

  • 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
    )

vkkfmsft_2-1632989191115.png

 

Best Regards,
Winniz

 

 

 

Dear Winniz,

 

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

Regards

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.