Problems creating a specific measure

[Anonymous]

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

[DemoFour]

Hi @Anonymous 

Can you confirm this data table is right and what it is you want to achive please. 

[Anonymous]

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

[richbenmintz]

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,

[Anonymous]

@richbenmintz 

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

[richbenmintz · Accepted solution]

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

[Anonymous]

Hi @richbenmintz 

You're legend. Thanks for the solution. I was not getting there for nothing. Need to start thinking when I need 2 measures or something. I'm trying to create always one.

Thank you very much.

Have a nice day.

Rui

[richbenmintz]

Hi @Anonymous,

You are most welcome, I would imagine you could get the same result using a single measure and storing the intitial distinct count in a variable.

Thanks,

[richbenmintz]

Hi @Anonymous,

Can you provide an expected outcome for you visual please?

[Anonymous]

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