topic Re: sum of blank rows across multiple values in DAX Commands and Tips

sum of blank rows across multiple values

edrft_99 — Wed, 23 Dec 2020 23:59:59 GMT

I feel silly that I can not get this one. But I am trying to make a measure that will give a sum for how many blank rows that fall under the below Hybrid row. The customer and owner are simple, but the Hybrid ones span both the owner and customer columns and for the life of me I cant get a measure to work correctly.

Thank you for any help.

Re: sum of blank rows across multiple values

lbendlin — Thu, 24 Dec 2020 00:51:20 GMT

Please provide sample data in usable format and show the expected outcome.

Re: sum of blank rows across multiple values

amitchandak — Thu, 24 Dec 2020 03:04:48 GMT

edrft_99 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

Re: sum of blank rows across multiple values

edrft_99 — Thu, 24 Dec 2020 03:35:22 GMT

Thank you for the response. Attached is a sample of the data and the expected output.

Re: sum of blank rows across multiple values

amitchandak — Thu, 24 Dec 2020 05:32:02 GMT

edrft_99 , You can have measures like these

Owner Responsibility = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Owner"))

Owner Implementation Missing = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Owner" && isblank(Table[Owner Implementation])))

Owner Implementation Missing = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Owner" && isblank(Table[Owner Test])))

Customer Responsibility = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Customer"))

Customer Implementation Missing = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Customer" && isblank(Table[Customer Implementation])))

Customer Implementation Missing = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Customer" && isblank(Table[Customer Test])))

Others you can create with help of these

Re: sum of blank rows across multiple values

edrft_99 — Thu, 24 Dec 2020 12:28:14 GMT

Thank you for that.

I was able to get those measures fairly eaisly, the one I was having issue with was the Dual Responsobility. I know the best way is a filter, I am just lost in the sauce on that one.

Re: sum of blank rows across multiple values

amitchandak — Thu, 24 Dec 2020 17:43:02 GMT

edrft_99 , to me these seem like

Dual Responsibility = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Dual"))
Owner Implementation Missing = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Dual" && isblank(Table[Owner Implementation])))
Owner Implementation Missing = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Dual" && isblank(Table[Owner Test])))

Customer Implementation Missing = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Dual" && isblank(Table[Customer Implementation])))
Customer Implementation Missing = calculate(count(Table[Responsibility]), filter(Table, Table[Responsibility] = "Dual" && isblank(Table[Customer Test])))