Forum Discussion

abc_777's avatar
abc_777
Solution Specialist
4 years ago
Solved

hi

Hello,

 

I want to count customer_code where customer_code starts with "90" or "91" and if there is a value in order_ID column

 

I have a measure as follows please help me to find the error

 

test =
IF(
    HASONEVALUE('bm_retail_t ssummary'[ORDER_ID]),
   COUNTROWS ( FILTER (ALL( 'bm_retail_t ssummary' ), LEFT ( 'bm_retail_t ssummary'[CUSTOMER_CODE], 2 ) = "90" || "91" ) )

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi abc_777 ,

    Try this measure 

    test =
    calculate(countrows(Sheet1),
    FILTER(sheet1,
    LEFT(Sheet1[Customer Code],2) = "90" || LEFT(Sheet1[Customer Code],2) = "91"
    ),
    FILTER(Sheet1,
    NOT(ISBLANK
    (Sheet1[Order ID])
    )
    )
    )
     

     

    Regards,

    Ashfiya

    --------------------------------------------------------------------------------------------------------------------------

    Did I help you today? Please mark my post as a solution and hit the Kudos button.

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file, whether it suits your requirement.

     

     

    test =
COUNTROWS (
    FILTER (
        'bm_retail_t ssummary',
        CALCULATE ( MAX ( 'bm_retail_t ssummary'[Order_ID] ) ) <> BLANK ()
            && OR (
                LEFT ( 'bm_retail_t ssummary'[CUSTOMER_CODE], 2 ) = "90",
                LEFT ( 'bm_retail_t ssummary'[CUSTOMER_CODE], 2 ) = "91"
            )
    )
) + 0

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file, whether it suits your requirement.

     

     

    test =
COUNTROWS (
    FILTER (
        'bm_retail_t ssummary',
        CALCULATE ( MAX ( 'bm_retail_t ssummary'[Order_ID] ) ) <> BLANK ()
            && OR (
                LEFT ( 'bm_retail_t ssummary'[CUSTOMER_CODE], 2 ) = "90",
                LEFT ( 'bm_retail_t ssummary'[CUSTOMER_CODE], 2 ) = "91"
            )
    )
) + 0
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi abc_777 ,

    Try this measure 

    test =
    calculate(countrows(Sheet1),
    FILTER(sheet1,
    LEFT(Sheet1[Customer Code],2) = "90" || LEFT(Sheet1[Customer Code],2) = "91"
    ),
    FILTER(Sheet1,
    NOT(ISBLANK
    (Sheet1[Order ID])
    )
    )
    )
     

     

    Regards,

    Ashfiya

    --------------------------------------------------------------------------------------------------------------------------

    Did I help you today? Please mark my post as a solution and hit the Kudos button.

     

    • abc_777's avatar
      abc_777
      Solution Specialist
      4 years ago

      thanks, both of you.

      I tried both measures and when I filter with invoice_DT (invoice Date) column it's not given me the right result. 

      on august I have 2 customers starting customer_code 91 and 90 but it gives me blank

       

      please help me how to filter to calendar date

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Please share your sample pbix file's link, and it will help me to investigate further to come up with a more accurate solution.

        Thanks.

    • abc_777's avatar
      abc_777
      Solution Specialist
      4 years ago

      Anonymous ,

       

      may i ask where you from?