Need help to select different character length using calulate

[Anonymous]

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 )

[v-yanjiang-msft]

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]

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?

[AllisonKennedy]

@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]

@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]

@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?

[Anonymous]

Hi @AllisonKennedy 

The part_no is comming from our coporate system and cannot change the raw data. Some part_no coming from the coprate system will have 00 on the end and the same part number might be put in without the 00 when data the part number is selected. I hope this help. Let me know if you need more clarity