How to find count of non blank rows based on a calculated measure ?

[TusharGaurav]

Hi Expert,

I have the following table visual:

Emp Name	Quantity Sold
John	122
Peter
Ravi	67
Rakesh
Neha	155

Here "Quantity Sold" is a calculated measure.

I need to find the number of non blank rows which is 3 this case i.e.

Emp Name	Quantity Sold	Count of rows
John	122	3
Peter		3
Ravi	67	3
Rakesh		3
Neha	155	3

I have tried using below dax:

COUNTROWS(FILTER('Order_table',NOT(ISBLANK([Quantity Sold]))))

But I am not getting the result.

Can you please suggest how to achieve the same?

Thanks and Regards,

Tushar Gaurav

[Ritaf1983 · Accepted solution]

Hi @TusharGaurav 
I checked it and allseseleted on table level works perfectly.
(it removes all the filters that come from the table and creates the filter by the measure)

**If you have employees or something similar as dimension it should be applied on this employees dimension table.

The updated Pbix is attached , from here if it won't work I need to see your pbix.

If my answer was helpful please give me a Kudos and accept as a Solution.

[Ritaf1983]

Hi @TusharGaurav 
You were on the right way 🙂

The formula that is working :

Non_blnk_rows =

CALCULATE(

COUNTROWS(FILTER('Table (2)',NOT ISBLANK([sum_]))),ALLSELECTED('Table (2)'[Emp Name]))

The pbix is attached

If my answer was helpful please give me a Kudos and accept as a Solution.

[TusharGaurav]

Hi Rita,

Thanks a lot for your Response.

I am sorry there are 2 more column i.e. Team Lead and Sales Person.

Manager Name	Team Lead	SalePerson	Quantity Sold
John	Rambo	Arnold	10000
John	Rambo	Tim	125
John	Rambo	Marry
Peter	Neha	Raj	123
Peter	Neha	Narnia
Rakesh	Ravi	Billy	45
Rakesh	Ravi	Martha	29
Rakesh	Ravi	Saif	35

I have used the below calculation:

Non_blnk_rows =
CALCULATE(
COUNTROWS(FILTER('Sheet1',NOT ISBLANK([Quantity Sold]))),ALLSELECTED('Sheet1'[Manager Name],Sheet1[Team Lead],Sheet1[Team Lead]))

But its not working and I am getting 1 in place of 6.

Can you please help.

[Ritaf1983]

Hi @TusharGaurav 
Modify the formula to :

Non_blnk_rows =

CALCULATE(

COUNTROWS(FILTER('Table (2)',NOT ISBLANK([sum_]))),ALLEXCEPT('Table (2)','Table (2)'[Quantity Sold]))

If my answer was helpful please give me a Kudos and accept as a Solution.

[TusharGaurav]

Hi Rita,

Thanks again for such prompt response.

Actually "Quantity Sold" is not a column but a calculated measure so I can use it in "All Except" dax.

Is there any alternative.

Thanks and Regards,

Tushar Gaurav

[Ritaf1983]

Allsellected (table ) sholud work too. 

I am not near the computer , if it wont work let me know and i will check later

[TusharGaurav]

Hi Rita,

Thanks.

Even allselected is not working.

This DAX is also taking column only and not measures.

Thanks and Regards,

Tushar Gaurav

[Ritaf1983 · Accepted solution]

Hi @TusharGaurav 
I checked it and allseseleted on table level works perfectly.
(it removes all the filters that come from the table and creates the filter by the measure)

**If you have employees or something similar as dimension it should be applied on this employees dimension table.

The updated Pbix is attached , from here if it won't work I need to see your pbix.

If my answer was helpful please give me a Kudos and accept as a Solution.

[Ritaf1983]

It should work because the filter context comes from table...

Please share the link for you pbix file and i will check why it is not working for you 

[TusharGaurav]

Hi Rita,

Thanks.

It working for my sample data of only one table ,but I am using multiple table with some complex calculation.

May be thats why its not working.

Thanks for your help.

Thanks and Regards,

Tushar Gaurav

[Ritaf1983 · Accepted solution]

@TusharGaurav 
In this case, it sounds like you definitely need a dimension table for employees.
Then, no matter what you do in the calculation, you'll be able to remove filters from the dimension table.
One of the reasons the Power BI engine highly favors "star schemas."