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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
b2wise
Helper III
Helper III

Compare two measures to evaluate dimension status

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".

 

ItemRegionSold Out ERPSold Out WMSFix
AppleTX00No
AppleCA00No
AppleNJ11No
AppleFL00No
AppleNY00No
OrangeTX00Yes
OrangeCA00Yes
OrangeNJ01Yes
OrangeFL00Yes
OrangeNY00Yes
PearTX11Yes
PearCA00Yes
PearNJ11Yes
PearFL00Yes
PearNY01Yes

 

Help me write the DAX formula for "Fix"!

@amitchandak @Greg_Deckler 

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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

 

MFelix_0-1677106735356.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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!

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MFelix
Super User
Super User

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

 

MFelix_0-1677106735356.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

@b2wise ,

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



b2wise
Helper III
Helper III

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

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

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

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

Idrissshatila
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




@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".

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.