Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help needed for supplier alignment checking column in Power BI desktop

Hi,

Please consider the following table:

PackageProjectSupplier
1100A1X

1100

A2X
1100C1Y
1100A3 
2200P1X

 

The request I got is to show if the group of projects (A1, A2, A3) get same vendors assigned on the tool, but for some reason, if the project in the group doesn't have the same supplier (as shown above, A3 has no assigned supplier) then in a new column I should show an expected supplier to be assigned on the project.

The other case would be if the A3 had another supplier (ex: A3 supplier assigned is Y) then the new column should show me, mismatch supplier.

 

Need Help
Show and Tell
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Anonymous ,

    You need to create the following column:

    P1 = LEFT('Table'[Project],1)
    P1 = LEFT('Table'[Project],1)
    maxcount1 = 
IF (
    'Table'[count1]
        = MAXX (
            FILTER ( 'Table', 'Table'[P1] = EARLIER ( 'Table'[P1] ) ),
            'Table'[count1]
        ),
    "match",
    IF (
        'Table'[count1]
            <> MAXX (
                FILTER ( 'Table', 'Table'[P1] = EARLIER ( 'Table'[P1] ) ),
                'Table'[count1]
            )
            && 'Table'[Supplier] = BLANK (),
        CALCULATE (
            MAX ( 'Table'[Supplier] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[P1] = EARLIER ( 'Table'[P1] )
                    && 'Table'[count1]
                        = MAXX ( FILTER ( 'Table', 'Table'[P1] = EARLIER ( 'Table'[P1] )),'Table'[count1]))),"mismatch" 
            )
        )

    Output refer:

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Those cases are identical. "No supplier"  is the same as "Different Supplier"

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    You need to create the following column:

    P1 = LEFT('Table'[Project],1)
    P1 = LEFT('Table'[Project],1)
    maxcount1 = 
IF (
    'Table'[count1]
        = MAXX (
            FILTER ( 'Table', 'Table'[P1] = EARLIER ( 'Table'[P1] ) ),
            'Table'[count1]
        ),
    "match",
    IF (
        'Table'[count1]
            <> MAXX (
                FILTER ( 'Table', 'Table'[P1] = EARLIER ( 'Table'[P1] ) ),
                'Table'[count1]
            )
            && 'Table'[Supplier] = BLANK (),
        CALCULATE (
            MAX ( 'Table'[Supplier] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[P1] = EARLIER ( 'Table'[P1] )
                    && 'Table'[count1]
                        = MAXX ( FILTER ( 'Table', 'Table'[P1] = EARLIER ( 'Table'[P1] )),'Table'[count1]))),"mismatch" 
            )
        )

    Output refer:

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien