Forum Discussion

menik_sun's avatar
menik_sun
Regular Visitor
5 years ago

Distinct count excluding blank rows in a matrix

Chart is like below Contract Type Nos Designation Location Permanent 1 Manager 1 London Permanent 1 Manager 1 Paris Permanent 1 Manager 1 New York Permanent 2 Manage...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

menik_sun , You can try measure like

 

three examples

 

calculate(distinctcount(Table[Nos]), filter(Table, not(isblank([Nos]))))

 

calculate(distinctcount(Table[Designation]), filter(Table, not(isblank([Nos]))))

 

calculate(distinctcount(Table[Designation]), filter(Table, not(isblank([Designation]))))

  • menik_sun's avatar
    menik_sun
    Regular Visitor
    5 years ago

    Thanks for your reply amitchandak . It's showing correct total but not while using field dimension. It is showing total result even after using field as rows.

     

    Below is what I get after using the formula:

     

    Thanks for your reply @amitchandak . It's showing correct total but not while using field dimension. It is showing total result even after using field as rows.

     

    Below is what I get after using the formula:


                                  With suggested formula         Formula required for
    Contract Type             Count of Nos                     Count of Nos
    Permanent                           7                                          3
    Contract                              7                                          4
    Total                                    7                                          7