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!