Forum Discussion

rdvasisht's avatar
rdvasisht
Frequent Visitor
3 years ago
Solved

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.
 
Need Help
Tips and Tricks
  • rdvasisht's avatar
    rdvasisht
    3 years ago

    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

  • rohit_singh's avatar
    rohit_singh
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      rdvasisht
      Frequent Visitor
      3 years ago

      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's avatar
        rohit_singh
        Icon for Solution Sage rankSolution Sage
        3 years ago

        Hi rdvasisht ,

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

        Kind regards,

        Rohit

  • halfglassdarkly's avatar
    halfglassdarkly
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

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