Sum of Distinct values

[Learner_SG]

Hi all,

I am trying to get the sum of the occupancy of these fields. But I need to get the distinct value sums only. How to add it in the measures.Could anyone help.

Male_toilet_occupancy_count =

CALCULATE (SUM( measurement[IOT status]),

FILTER ( 'measurement',measurement[Is IN 30Minutes]=1 && measurement[object.Document.objects.name] in{ "WC Glass Flush Valve M_01","WC Glass Flush Valve M_02","WC Glass Flush Valve M_03","WC Glass Flush Valve M_04","WC Glass Flush Valve M_05","Urinal Glass Flush Valve M_01","Urinal Glass Flush Valve M_02","Urinal Glass Flush Valve M_03","Urinal Glass Flush Valve M_04","Urinal Glass Flush Valve M_05","Urinal Glass Flush Valve M_06","Urinal Glass Flush Valve M_07"}))+0

[Learner_SG]

@PC2790  thanks,it worked.

[PC2790]

You canmake use of SUMX and distinct i like:

SUMX(DISTINCT(measurement[IOT status]),measurement[IOT status])

[amitchandak]

@Learner_SG m Try like

CALCULATE (SUMX(summarize(measurement, measurement[IOT status]), [IOT status]),
FILTER ( 'measurement',measurement[Is IN 30Minutes]=1 && measurement[object.Document.objects.name] in{ "WC Glass Flush Valve M_01","WC Glass Flush Valve M_02","WC Glass Flush Valve M_03","WC Glass Flush Valve M_04","WC Glass Flush Valve M_05","Urinal Glass Flush Valve M_01","Urinal Glass Flush Valve M_02","Urinal Glass Flush Valve M_03","Urinal Glass Flush Valve M_04","Urinal Glass Flush Valve M_05","Urinal Glass Flush Valve M_06","Urinal Glass Flush Valve M_07"}))+0

[Learner_SG]

@amitchandak @PC2790  both options are not giving me the correct answer. Its the measurement[object.Document.objects.name which will be occuring more than once, so is the distinct/summarize to be placed there? 

[PC2790]

Can you provide your sample data please? It'll be easier to give the solution and also try:

SUMX( Distinct(measurement[object.Document.objects.name]),Calculate(Sum(measurement, measurement[IOT status]),FILTER ( 'measurement',measurement[Is IN 30Minutes]=1))

[Learner_SG]

@PC2790  I have extracted the 3 tables sample data and pasted it below. Basically, my condition is that when Is in 30 minutes column is 1 and I need to sum up all the male toilets occupied which is known by the IOT status as 1.  for the example stated above , it sums up all the male toilets denoted by the names in it . but what happens is that , it does not distincts out  and I get duplicated counts.below is the data

object.Document.objects.name	Is IN 30Minutes	IOT status
WC Glass Flush Valve F_05	1	1
WC Glass Flush Valve M_04	1	1
WC Glass Flush Valve HCP_01	1	1
WC Glass Flush Valve M_03	1	1
WC Glass Flush Valve F_05	1	1
WC Glass Flush Valve F_05	1	1
WC Glass Flush Valve F_05	1	1
WC Glass Flush Valve F_16	1	1
WC Glass Flush Valve F_16	1	1
WC Glass Flush Valve F_16	1	1
WC Glass Flush Valve F_10	1	1
WC Glass Flush Valve F_05	1	1
Deck Mounted Sensor Tap F_02	1	1
Deck Mounted Sensor Tap F_02	1	1
Deck Mounted Sensor Tap M_04	1	1
Deck Mounted Sensor Tap M_03	1	1
Deck Mounted Sensor Tap M_03	1	1
Deck Mounted Sensor Tap F_09	1	1
Deck Mounted Sensor Tap F_09	1	1
Deck Mounted Sensor Tap F_09	1	1
Deck Mounted Sensor Tap M_02	1	1
Deck Mounted Sensor Tap M_02	1	1
Deck Mounted Sensor Tap M_05	1	1
Deck Mounted Sensor Tap M_05	1	1
Deck Mounted Sensor Tap F_02	1	1
Deck Mounted Sensor Tap F_02	1	1
Deck Mounted Sensor Tap F_02	1	1
Deck Mounted Sensor Tap F_02	1	1
Deck Mounted Sensor Tap F_02	1	1

 from this if i need to count the male toilets occupied , i need to distinctly count them fyi these are the male toilet/urinal names "WC Glass Flush Valve M_01","WC Glass Flush Valve M_02","WC Glass Flush Valve M_03","WC Glass Flush Valve M_04","WC Glass Flush Valve M_05","Urinal Glass Flush Valve M_01","Urinal Glass Flush Valve M_02","Urinal Glass Flush Valve M_03","Urinal Glass Flush Valve M_04","Urinal Glass Flush Valve M_05","Urinal Glass Flush Valve M_06","Urinal Glass Flush Valve M_07. 

for this case   there are only 2 male toilet occupied (distinct)and for female also it should be 4 ,

hope my question is clear. thanks for the help.

[PC2790 · Accepted solution]

Alright!  I would suggest to have less hardcoded values so I have created a separate table containing the list of Male toilets and created a measure which will compare the values one by one and if it is a male toilet, will add to the distinct count(I have taken distinct count as the IOT status for all of them is 1. I have taken it into account as a filter.

Here is the Dax code for measure:

Output:

and the table containing list of male toilets looks like: