Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Need help to select different character length using calulate

Hi All
I have the measure below that works correctly but need help to select all the part_no in the table, in which the character length ranges from 4 to 21 . As you can see I started off with Len=7. How do I format the measure below to look at all the parts_no that has a Len from 4 to 21.
 

P360PN = VAR AA =

    LEN( [part_no] )

VAR BB =

    CALCULATE(FIRSTNONBLANK('P360'[Primary Item Number], 1), FILTER(ALL('P360'),'P360'[Engineering Item Number]= 'cqms_mnc_data_v'[part_no] ) )

RETURN

    IF( AA = 7, [part_no], BB )

Need Help
Show and Tell

6 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous  You can use the && for and filter, which will give you between:

     

    P360PN = VAR AA =

        LEN( [part_no] )

    VAR BB =

        CALCULATE(FIRSTNONBLANK('P360'[Primary Item Number], 1), FILTER(ALL('P360'),'P360'[Engineering Item Number]= 'cqms_mnc_data_v'[part_no] ) )

    RETURN

        IF( AA <= 21 && AA >= 4, [part_no], BB )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      AllisonKennedy 

      The measure work but didnt give me the desire result like the orginal measure. With my orginal measure the dsire reult was correctl as i needed the measure  as i needed to pull in the primary item number info as seen below in the table using the length of 7

      When I use the  updated measure using  IF( AA <= 21 && AA >= 4, [part_no], BB ). I get the following but not the correct  as the P360PN should be 3017993

       

       

       

       

       

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous It looks like your measure for [part_no] needs fixed in this case. What is the DAX for part_no please and how do you know you want to exclude the last 2 zeros?

         

         

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, in my understanding, you want to remove all 0 after a number, if this is the case, here's my solution.

    1. In Power Query, change the column data type to Text.

    2. Create three custom columns.

    Custom = Text.Reverse([part_no])
    Custom.1 = Text.Trim([Custom],"0")
    Custom.2 = Text.Reverse([Custom.1])

    Then remove unwanted columns, get the result.

    I attach the sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • AllisonKennedy's avatar
      AllisonKennedy
      Icon for Community Champion rankCommunity Champion
      4 years ago

      UPDATE: I would use "00" to make this a bit more precise - Anonymous are there any part numbers that end in 00 where you want to keep the 00?

       

       

      v-yanjiang-msft  You don't need the reverse steps in here.

       

      Anonymous  Is it just 0s at the end of your part numbers that you need to remove? Is there ever a 0 at the end that needs to stay? if so, we'll need more info about how to know what to keep.

       

      However, if you always want to remove the leading and trailing zeros, then as v-yanjiang-msft  suggested, in Power Query you can add a custom column to Trim the 0s from your Part_no that comes from the system. Does that make sense?