Skip to main content
cancel
Showing results for 
Search instead 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

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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors