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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Saxon10
Post Prodigy
Post Prodigy

COUNTROW based on the Text

 

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:

 

ITEMSALES CODEDESIRED RESULT
123459001NOT OKAY
123459002NOT OKAY
123459003NOT OKAY
123459004NOT OKAY
123459005OKAY
123459006OKAY
123459007OKAY
123459008OKAY
123459009OKAY
123459010OKAY
12345DMKOKAY
12345  
546849005OKAY
546849006OKAY
546849007OKAY
546849008OKAY
546849009OKAY
546849010OKAY
546849011OKAY
54684  
8979001NOT OKAY
8979002NOT OKAY
8979003NOT OKAY
8979004NOT OKAY
897  
5642319004NOT OKAY
5642319010OKAY
5642319011OKAY
5642319003NOT OKAY
5642319005OKAY
5642319006OKAY
5642319002NOT OKAY
5642319001NOT OKAY

 

 

I am trying to applying the following DAX but it's giving wrong result.

 

Can you please advise. 

 

RESULT = COUNTROWS(FILTER(DS,DS[ITEM]=EARLIER(DS[ITEM]) && DS[SALES CODE] IN {"9001","9002","9003","9004"}))
 
Saxon10_0-1619566640313.png

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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

v-yalanwu-msft_0-1620092917368.jpeg

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:

v-yalanwu-msft_1-1620092922490.jpeg

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.

View solution in original post

9 REPLIES 9
v-yalanwu-msft
Community Support
Community Support

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

v-yalanwu-msft_0-1620092917368.jpeg

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:

v-yalanwu-msft_1-1620092922490.jpeg

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.

 

Jihwan_Kim
Super User
Super User

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.

 

Picture3.png

 

 

 

RESULT =
IF (
DATA[SALES CODE] = BLANK (),
BLANK (),
IF (
Data[SALES CODE] = "9001"
|| Data[SALES CODE] = "9002"
|| Data[SALES CODE] = "9003"
|| Data[SALES CODE] = "9004",
"Not OK",
"OK"
)
)
 
 
WITH ITEM =
IF (
COUNTROWS (
FILTER (
DATA,
DATA[ITEM] = EARLIER ( DATA[ITEM] )
&& DATA[SALES CODE] IN { "9001", "9002", "9003", "9004" }
)
) > 0,
"OKAY",
"NOT OKAY"
)

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.


Go to My LinkedIn Page


Ashish_Mathur
Super User
Super User

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.

Untitled.png


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

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.

 

Saxon10_0-1619602109425.png

 

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. 


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

 

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.

 

RESULT WITH ITEM = IF(COUNTROWS(FILTER(DATA,DATA[ITEM]=EARLIER(DATA[ITEM]) && DATA[SALES CODE] IN {9001,9002,9003,9004})),"OKAY","NOT OKAY")
 
Can you please provide new solution incuding item column.
Saxon10_0-1619605244972.png

 

 

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. 


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

Thanks for your reply and help.

Still it's giving wrong results.

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.