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
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!
Solved! Go to 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"
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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!
Hi,
Share the download link of the PBI file.
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êsThanks @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
Proud to be a Super User!
Check out my blog: Power BI em Português@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?
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 👍
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".
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |