cancel
Showing results for
Did you mean: Frequent Visitor

## filtering a column based on length of string

hi all experts,

i have this following DAX:

test = (CALCULATE(COUNT(individuals[ind_city]),

FILTER(individuals,LEN(individuals[ind_city] <"1")))/

(CALCULATE(COUNT(individuals[ind_id]))))

this gives me this result = 1.00

i am not sure what is wrong with this this. i have 7000 city cells in ind_city column that are null and 0 length in character (for some reaons).

i need to calculate percent of cells whose length of character is <1. I have been adding an extra column to get the length first and then the formula works to get the percent. but this is very time consuming and i am wondering if i can do this without creating an extra column for the lenght.

table = individuals
ind_city = column (string)
ind_id = column(int)

any help is appreciated.

1 ACCEPTED SOLUTION Frequent Visitor hi @rohit_singh  here is the sample. in the filter you can see that ther are 13 blank and 7083 empty. when i cross checked this in postgresql i saw that powerbi shows the percent for empty cells when using blank() and not the percent for null values.

but there is a solution for this that i got with following dax:

NB Cities fields = (CALCULATE(COUNTROWS(individuals),

FILTER(individuals,individuals[ind_city] = blank()))/

((CALCULATE(COUNTROWS(individuals)))))

using COUNTROWS i got both blank and empty values. earlier i was not using countrows

Thanks for your help in this @rohit_singh
6 REPLIES 6  Resolver II

One hack that might help is appending a blank text string to your [ind_city] column with &""

rather than needing to check for length you could check for [ind_city]&""=""  Super User

Hi @rdvasisht ,

Please try chaning the measure to this :

test =

var _null =
CALCULATE(
COUNT(individuals[ind_id]),
FILTER(individuals, individuals[ind_city] = BLANK()
)
)

var _total = COUNT(individuals[ind_id])
RETURN
DIVIDE(_null,_total,0)

Input Output When calculating blanks, change the count of individuals[ind_city] to individuals[ind_id] as highlighted in yellow above.

Kind regards,

Rohit Frequent Visitor

hi @rohit_singh  thanks for looking into this. for some reasons i have null values (1000) and empty values(6000) so if i use blank in DAX then it gives me percent of blank values and ignores the 6000 cells ( not sure why). that is why i needed to use len in the measure. or may be is there a workaround for this. i tried to use isempty() which ddi not work.  Super User

Hi @rdvasisht ,

Could you share a sample of your data? Especially the null and empty values if possible,

Kind regards,

Rohit Frequent Visitor hi @rohit_singh  here is the sample. in the filter you can see that ther are 13 blank and 7083 empty. when i cross checked this in postgresql i saw that powerbi shows the percent for empty cells when using blank() and not the percent for null values.

but there is a solution for this that i got with following dax:

NB Cities fields = (CALCULATE(COUNTROWS(individuals),

FILTER(individuals,individuals[ind_city] = blank()))/

((CALCULATE(COUNTROWS(individuals)))))

using COUNTROWS i got both blank and empty values. earlier i was not using countrows

Thanks for your help in this @rohit_singh  Super User

Happy to hear you were able to get the intended result @rdvasisht . Kindly mark your answer as the accepted solution for this post to help others as well.  