Calculate count of application which has blank sales based on category selections

[mahg1kor]

Hello All,

I have data of applications and their sales for each category wise.

now to get the count of applications which has blank or 0 sales for each category wise i can directly do the DISTINCTCOUNT of applications and it will do the need.

But, the my customer is looking for a count in a different way.

Below is the sample data that i have.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZVRa4MwFIX/ysXnDnSFMR+v1m7dVidm7TZKkZBaF6oGkshgv34O1tFq7Szx0XNyvpsTQlytrOspwpJLXdEcSEGlBp/KjTWyYpGnCgquFC8z4CXIWrhiVNNcZLVvW+vRyhq/34FH2U5st5ylv7GYq536W3I7AZz1BuL4xnXs+hMZE1WpVbeTfIokxrY/F3Fb/JmO9eisLNKT1PbGj6wu+f9Crms7zslCTadZaO8fFdqLnYUOFzQKHVpdcv9CBKOkMQABC/olSlim5UZI8OutSZonuOiP72J4sTnDR3PGJDBnBMScMR3gPGYv5oyHyJwxfzNnhE/mjGgABhngfiweB2CQM0/L2VD/wVGI4D0T//6SlIevBII4gjDVquI6vSgsFPsArLSAqP5bqX7Z9Tc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, Category = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Application", type text}, {"Category", type text}, {"Sales", Int64.Type}})
in
    #"Changed Type"

 Now the data looks as below image.

The ask is, I have total 8 categories of data, in which

When i dont filter or select any value from category, it should consider all the categories and show the applications which has zero or blank sales for all the categories.

in this case, the count of applications which has 0 or blank sales for all categories are 1 - A99011.

When i select accounts category, the count is 2 - A36910, A99011

But when i do not select any categories it shows the count as 22 sa i took distinct count of applications but in my case it should give only 1 as there is only one application which has all categories data as blank.

Please help.

Thanks,

Mahesh V.

[mahg1kor]

@IIPowerBlog i have provided the power query with sample data.

It is the data that i am working with.

[mahg1kor]

select Application,COUNT(Indicator) IndCnt, SUM(ISNULL([Day-01],0)) sm
from Report Check Data
group by Application
Having --COUNT(Indicator) = 8 and
SUM(ISNULL([Day-01],0)) = 0
order by 2 desc

this is my sql query 
i need to convert this into Power Dax, can anyone please help me on this.

[mahg1kor]

I have already provided the sample data for  powerquery...

[mahg1kor]

Can anyone please help me on this..

[IIPowerBlog]

hello @mahg1kor . Have you tried to select all categories? What happens then? You can simply do this in the slicer by showing the 'Select All' option and running your report by default on it. Does this help?

[mahg1kor]

@IIPowerBlog the selection is taking the OR condition while doing the distinctcount and hence I am getting the 22 as count.

But it should be AND condition, so that, we are making sure that if all are selected means, all categories values should be 0 and give the count accordingly.

[mahg1kor]

@IIPowerBlog thanks for the reply.

I did not uunderstood how select all can help me to get the distinct count of applications which has all categories as blank.

Even though i did it, still showing count as 22.

as i mentioned in the question, it should be 1, as there is only one application has all zero values for all the categories.

Eventually, what i am asking here is the other way of what power bi default feature does.

If you select all the categories means, it should give the count of all applications which has 0 for all the categories and if you select one or two or any, it should give accordingly.

Hope i made it clear for you.

Let me know if you need anymore details.

Regards,

Mahesh V

[IIPowerBlog]

hi again @mahg1kor  it looks like your data source is the reason. can you please share this so I can understand better? thank you.