topic Re: Sum up the distinct counts with a filter in DAX Commands and Tips

Sum up the distinct counts with a filter

Anonymous — Wed, 19 Aug 2020 00:17:12 GMT

Hello,

I have a table that has for each hour if any assets of ours were down.

I have the following calulation which serves my purposes :

Hours Down = CALCULATE(DISTINCTCOUNT(Site_Availability_Historyz_Data[Date_Time_History]),Site_Availability_Historyz_Data[AssetPK]<>BLANK())

This gives me the all of the hours that were down at a given site regardless if there were 3 rows for a given hour even though 3 assets could have been down, and therefore 3 unique rows for 15:00 hours.

However, the total within the table is not summing the distinct counts. I know I need to use a sumx, but I have having trouble with the syntax. Can anyone help? Initally, I tried the following:

SUMX(Site_Availability_Historyz_Data,CALCULATE(DISTINCTCOUNT(Site_Availability_Historyz_Data[Date_Time_History]),Site_Availability_Historyz_Data[AssetPK]<>BLANK()))

however, certain rows become inaccurate because it is essentially doubling the count of hours when there are two different assets down at a given hour.

Any advice?

Re: Sum up the distinct counts with a filter

Anonymous — Wed, 19 Aug 2020 00:57:30 GMT

Advice is: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Sum up the distinct counts with a filter

mahoneypat — Wed, 19 Aug 2020 02:26:42 GMT

Not totally clear but the issue is probably the table in your SUMX. Please try this pattern in your measure

NewMeasure = SUMX(VALUES(Table[Column]), CALCULATE(DISTINCTCOUNT(Site_Availability_Historyz_Data[Date_Time_History]),Site_Availability_Historyz_Data[AssetPK]<>BLANK()))

Replace Table[Column] with the one used in your table visual. If you have more than one column in your visual, you will need to replace the VALUES( ) expression with a SUMMARIZE one. For example, SUMMARIZE(Table, Table[Column1], Table[Column2]).

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Sum up the distinct counts with a filter

Anonymous — Wed, 19 Aug 2020 16:24:21 GMT

This is my data and how the measure is turning out.

You'll see that the distinct count will get me 744, which is what I want, but the sumx measure to sum is getting me 921.

I'll attatch July's data here https://1drv.ms/u/s!Ag08lU4J4vcXslqhttwvvNGL2vEm?e=R4KRAu

Re: Sum up the distinct counts with a filter

Anonymous — Wed, 19 Aug 2020 23:18:01 GMT

Still having issues with summarize as its counting duplicates values of column A when column B is different. Would a group by work better?

Re: Sum up the distinct counts with a filter

mahoneypat — Thu, 20 Aug 2020 01:01:44 GMT

Thank you for providing sample data. Not sure if this is what you are looking for, but here is a SUMX measure that returns 744 as the result. Your sample data only has 1 site value. Does your actual data have multiple Site values? If so, this measure will calculate the # of down hours for each site and include all in the total.

Hours Down =
SUMX (
VALUES ( 'in'[Site] ),
CALCULATE ( DISTINCTCOUNT ( 'in'[Date_Time_History] ) )
)

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Sum up the distinct counts with a filter

Anonymous — Fri, 21 Aug 2020 18:29:46 GMT

Thank you so much! Can you explain to me why you used sites after values instead of date_Time_history?