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])))) ...
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 
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

      • rdvasisht's avatar
        rdvasisht
        Frequent Visitor
        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