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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Thanks in advance!

2 ACCEPTED SOLUTIONS
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"
)``````

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

Helper III

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!

9 REPLIES 9
Super User

Hi,

Share the download link of the PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
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"
)``````

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

Helper III

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!

Super User

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

Helper III

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

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

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

Helper III

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

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
 Vote for my Community Mobile App Idea

Proud to be a Super User!

Helper III

@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

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors