filtering a column based on length of string

[rdvasisht]

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.

[halfglassdarkly]

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]&""=""

[rohit_singh]

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

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

[rdvasisht]

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.

[rohit_singh]

Hi @rdvasisht ,

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

Kind regards,

Rohit

[rdvasisht · Accepted solution]

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 

[rohit_singh]

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.