cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper III

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 (
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,

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper III

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 (
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

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.

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors