Compare two measures to evaluate dimension status

[b2wise]

Hi All,

I'm stuck on something that seems easy. I'm not using my actual data to try and keep things simple.

Essentialy I am comparing the last two "Sold Out" columns (which are measures within a table visual) to each other. If there is a mismatch for any region I would like to mark the entire item as "Fix".

Item	Region	Sold Out ERP	Sold Out WMS	Fix
Apple	TX	0	0	No
Apple	CA	0	0	No
Apple	NJ	1	1	No
Apple	FL	0	0	No
Apple	NY	0	0	No
Orange	TX	0	0	Yes
Orange	CA	0	0	Yes
Orange	NJ	0	1	Yes
Orange	FL	0	0	Yes
Orange	NY	0	0	Yes
Pear	TX	1	1	Yes
Pear	CA	0	0	Yes
Pear	NJ	1	1	Yes
Pear	FL	0	0	Yes
Pear	NY	0	1	Yes

Help me write the DAX formula for "Fix"!

@amitchandak @Greg_Deckler 

Thanks in advance!

[MFelix · Accepted solution]

Hi @b2wise ,

Try the following code:

Fix =
VAR temp_table =
    CALCULATETABLE (
        GROUPBY (
            SUMMARIZE (
                'Table',
                'Table'[Region],
                'Table'[Item]
                "TEST", [Sold Out ERP M] = [Sold Out WMS]
            ),
            'Table'[Item],
            [TEST]
        ),
        REMOVEFILTERS ( 'Table'[Region] )
    )
RETURN
    IF (
        CONTAINSSTRING ( CONCATENATEX ( temp_table, [TEST] ), "FALSE" ),
        "YES",
        "NO"
    )

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[MFelix · Accepted solution]

Hi @b2wise ,

Try the following code:

Fix =
VAR temp_table =
    CALCULATETABLE (
        GROUPBY (
            SUMMARIZE (
                'Table',
                'Table'[Region],
                'Table'[Item]
                "TEST", [Sold Out ERP M] = [Sold Out WMS]
            ),
            'Table'[Item],
            [TEST]
        ),
        REMOVEFILTERS ( 'Table'[Region] )
    )
RETURN
    IF (
        CONTAINSSTRING ( CONCATENATEX ( temp_table, [TEST] ), "FALSE" ),
        "YES",
        "NO"
    )

[b2wise · Accepted solution]

Thanks @MFelix  for answering my question. Your measure works!

I didn't get a chance to post but I asked this question to ChatGPT and it gave me a similar formula but it didn't work. I changed ChatGPT's code a little and that works as well.

Fix Measure = 
CALCULATE(IF (
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    'test table',
                    'test table'[Item],
                    'test table'[Region],                    
                    "SoldOutERP", SUM ( 'test table'[Sold Out ERP] ),
                    "SoldOutWMS", SUM ( 'test table'[Sold Out WMS] )
                ),
                "Mismatch", [SoldOutERP] <> [SoldOutWMS]
            ),
            [Mismatch] = TRUE()
        )
    ) > 0,
    "Yes",
    "No"
)
, ALL('test table'[Region])
)

Thanks again!

[MFelix]

@b2wise ,

Dont forget to accept the correct answers so it can help others.

[b2wise]

@Ashish_Mathur @MFelix @lbendlin Top solution authors, can you help me with this?

[lbendlin]

last two "Sold Out" columns (which are measures within a table visual) 

Please show the measure code, or provide a sample pbix.

[b2wise]

@v-yanjiang-msft You helped me on a different question recently. Can you help me with this?

[Idrissshatila]

Hello @b2wise ,

can you explain when the fix column should show yes and when no, the condition that determines.

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[b2wise]

@Idrissshatila Sorry if I wasn't clear. Compare the 1's and 0's in the two columns "Sold Out ERP" and "Sold Out WMS" by each item and region. If there are any mismatches the entire item should be marked as fix.

For item "Apple" all match hence "No", for Orange NJ and Pear NY there are discrepancies so the entire item should be marked as "Yes".