Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rdvasisht
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

rdvasisht_0-1670014865088.png

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 

View solution in original post

6 REPLIES 6
halfglassdarkly
Resolver IV
Resolver IV

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

rohit_singh_0-1670005850329.png

Output

rohit_singh_1-1670006318714.png

 
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!


 

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.

Hi @rdvasisht ,

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

Kind regards,

Rohit

rdvasisht_0-1670014865088.png

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 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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