cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Responsive Resident

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

Solution Sage

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.

Solution Sage

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
Solution Sage

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors