Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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:
You canmake use of SUMX and distinct i like:
SUMX(DISTINCT(measurement[IOT status]),measurement[IOT status])
@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
@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?
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))
@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.
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |