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: Package Project Supplier 1100 A1 X 1100 A2 X 1100 C1 Y 1100 A3   2200 P1 X   The request I got is to show if the group ...
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

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