Forum Discussion
Problems creating a specific measure
Hello all,
I'm having some difficulties to implment a measure or a workaround to do the following:
I need to implement a stacked bar chart to see the "Executed Quantity Batch by Operator".
In the axis I have the operator but my problem is to create a measure to calculate the rest.
In sql I have the following table (the data is not complete, it's just for the example):
| Id | DimVoiceOperator_id | DimStartDate_id | BatchNumber | FactTaskExecution_id |
| 117943 | 9 | 08/07/2021 | 6 | 2717 |
| 117944 | 9 | 08/07/2021 | 6 | 2717 |
| 118016 | 9 | 08/07/2021 | 2 | 2774 |
| 118017 | 9 | 08/07/2021 | 1 | 2774 |
| 118018 | 9 | 08/07/2021 | 2 | 2774 |
| 118158 | 9 | 08/07/2021 | 1 | 2787 |
| 118159 | 9 | 08/07/2021 | 1 | 2787 |
| 118160 | 9 | 08/07/2021 | 1 | 2787 |
| 118161 | 9 | 08/07/2021 | 1 | 2787 |
| 118204 | 9 | 08/07/2021 | 1 | 2802 |
| 118205 | 9 | 08/07/2021 | 1 | 2802 |
| 118206 | 9 | 08/07/2021 | 1 | 2802 |
And in my sql query I have the following which gives me the expected result:
select sum(batch) from (
select count(distinct(batchnumber)) as batch
FROM [Sonae.Voice.202x].[dbo].[FactTaskDetailExecution]
where DimStartDate_id = '2021-07-08' and DimVoiceOperator_id = 9
group by FactTaskExecution_id) as total
The where clause I have the operator in the Axis of the chart and the date is in a slicer so I just need to understand how to apply the sum, the count and the group by.
Can anyone have some tips on this?
Best regards,
Rui Santos
Hi Anonymous
What you need is a Sumx of the Distinct count, forgive the bad measure name
sumx distinct count = SUMX(VALUES('Table'[FactTaskExecution_id]), [Distinct Batch Count])Again pbix sample attached
9 Replies
Hi Anonymous,
Can you provide an expected outcome for you visual please?
Hello richbenmintz ,
Thanks for you quick reply.
Yes, looking at that data the result should be:
- 1 distinct for FactTaskExecution_id = 2717
- 2 disctinct for FactTaskExecution_id = 2774
- 1 distinct for FactTaskExecution_id = 2787
- 1 distinct for FactTaskExecution_id = 2802
So the final result should be the sum of the above values which is 5.
Regards,
Rui
Hi Anonymous,
Making some assumptions on the Visual requirement, the following measure provides the distinct count of Batches
Distinct Batch Count = DISTINCTCOUNT('Table'[BatchNumber])Resulting in the following visual
Attached please find a simple sample file
Thanks,
Thanks, it's almost there. I just need to group by the FactTaskExecution_id becaus if batchnumber = 1 in two different FactTaskExecution_id it should count as well if you know what I mean.
Thank you.
Rui
Hi Anonymous
What you need is a Sumx of the Distinct count, forgive the bad measure name
sumx distinct count = SUMX(VALUES('Table'[FactTaskExecution_id]), [Distinct Batch Count])Again pbix sample attached
Hi Anonymous
Can you confirm this data table is right and what it is you want to achive please.
Hello, DemoFour
I want to show in one stack bar chart the Executed Batches By Operator. Looking at the data the expected result should be:
- 1 distinct for FactTaskExecution_id = 2717
- 2 disctinct for FactTaskExecution_id = 2774
- 1 distinct for FactTaskExecution_id = 2787
- 1 distinct for FactTaskExecution_id = 2802
So the final result should be the sum of the above values which is 5.
However using just the distinctcount is not enough because different FactTaskExecution_id can have the same batchnumber and I want the sum of them. Don't know if you understand..
Thanks,
Rui
