Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

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 sale...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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.

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago

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.

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

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. 

  • Saxon10's avatar
    Saxon10
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

     

    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.

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      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. 

      • Saxon10's avatar
        Saxon10
        Icon for Post Prodigy rankPost Prodigy
        5 years ago

        Thanks for your reply and help.

        Still it's giving wrong results.