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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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