Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
The same item has the following sales code from 9001 to 9010 and DMK in data table but I am intersted the following codes only "9001,9002,9003,9004"
if same item has the following sales code conatin "9001,9002,9003,9004" then "Not Okay" and if not then return "okay" and if blanks then return as a blanks.
Data table:
ITEM | SALES CODE | DESIRED RESULT |
12345 | 9001 | NOT OKAY |
12345 | 9002 | NOT OKAY |
12345 | 9003 | NOT OKAY |
12345 | 9004 | NOT OKAY |
12345 | 9005 | OKAY |
12345 | 9006 | OKAY |
12345 | 9007 | OKAY |
12345 | 9008 | OKAY |
12345 | 9009 | OKAY |
12345 | 9010 | OKAY |
12345 | DMK | OKAY |
12345 | ||
54684 | 9005 | OKAY |
54684 | 9006 | OKAY |
54684 | 9007 | OKAY |
54684 | 9008 | OKAY |
54684 | 9009 | OKAY |
54684 | 9010 | OKAY |
54684 | 9011 | OKAY |
54684 | ||
897 | 9001 | NOT OKAY |
897 | 9002 | NOT OKAY |
897 | 9003 | NOT OKAY |
897 | 9004 | NOT OKAY |
897 | ||
564231 | 9004 | NOT OKAY |
564231 | 9010 | OKAY |
564231 | 9011 | OKAY |
564231 | 9003 | NOT OKAY |
564231 | 9005 | OKAY |
564231 | 9006 | OKAY |
564231 | 9002 | NOT OKAY |
564231 | 9001 | NOT OKAY |
I am trying to applying the following DAX but it's giving wrong result.
Can you please advise.
Solved! Go to Solution.
Hi, @Saxon10 ,
You could create measure and column by the following formula:
Step1:create a column
WITHITEM = SWITCH(TRUE(),[SALES CODE] in {"9001","9002","9003","9004"},"NOT OKAY",[SALES CODE]<>BLANK(),"OKAY")
And if you want to COUNTROW based on the Text:
count = CALCULATE(COUNTROWS('DATA'),FILTER(ALLEXCEPT('DATA','DATA'[ITEM]),[SALES CODE] in{"9001","9002","9003","9004"}))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Saxon10 ,
You could create measure and column by the following formula:
Step1:create a column
WITHITEM = SWITCH(TRUE(),[SALES CODE] in {"9001","9002","9003","9004"},"NOT OKAY",[SALES CODE]<>BLANK(),"OKAY")
And if you want to COUNTROW based on the Text:
count = CALCULATE(COUNTROWS('DATA'),FILTER(ALLEXCEPT('DATA','DATA'[ITEM]),[SALES CODE] in{"9001","9002","9003","9004"}))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for your reply and sorry for the late reply.
Your formula working well.
Hi, @Saxon10
Try the below two for creating new columns.
Sorry to say that I still quite do not understand what you want to show and analyze, but I just wanted to get rid of the error message first, then I hope you can step further from here.
Please let me know how your desired outcome looks like.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
This calculated column formula works
=if(ISBLANK(Data[SALES CODE]),BLANK(),if(Data[SALES CODE]="9001"||Data[SALES CODE]="9002"||Data[SALES CODE]="9003"||Data[SALES CODE]="9004","Not OK","OK"))
Hope this helps.
Hi,
Thanks for your reply.
When I try to apply your formula into Power Bi it's giving wrong result were contain blanks.
Could you please add item criteria part of your formula please. I am looking item level not only for sales code.
Hi,
Your and my result matches without my formula considering the Item Code column. Hoever, i am surprised that a blank in the Sales code column is returning OK in the Result column. The initial part of my formula clearly states that if the Sales code is blank, then return blank.
Share the link from where i can download your PBI file.
Thanks for your respones again.
Here is the file for your reference https://www.dropbox.com/s/ux966h65am91wx2/IN.pbix?dl=0
I am trying to inculde the item by using the following DAX formula but I am receving error.
Hi,
The entries in the desired result column do not match with the entries in the WIth Item column column but atleast your error is gone away with this formula
WITH ITEM = IF(CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[ITEM]=EARLIER(DATA[ITEM]) && DATA[SALES CODE] IN {"9001","9002","9003","9004"})),"OKAY","NOT OKAY")
I guess that is all i can help you with.
Thanks for your reply and help.
Still it's giving wrong results.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |