count numbers in calculated row

[tproost]

Hi,

I have a table of Presence of certain employees at certain events.

for each event it will list the eventnumber and employee name in a row.

Richt now in the report i just count the occurences and it tells me for each employee how many events they attende within a filtered period of time.

Event	Employee
1	a
1	b
2	a
2	b
3	b

The problem occurs when i try to list how may employees have attended how many events.

e.g.

# of events	# of employees
3	1
2	1
1	0

We need this data because they are required to attend certain amount of events in a given period. this will help us track how many have not completed it.

Optimaly i find a sollution that still allows me to filter based on Time periods.

I have been looking at measures but I can't manage to get the correct output.

Thanks in advance.

[Ashish_Mathur]

Hi,

The numbers shown in the result table seems wrong.  the number of people who attended 3 events should be 0.  Please show the expected result very clearly.

[v-yangliu-msft]

Hi  @tproost ,

I created some data:

Here are the steps you can follow：

Way1：
Set [Employee] to Count (Distinct)

Ways2：

Create measure.

Measure =
var _mindate=MINX(ALLSELECTED('Table'),[Date])
var _maxdate=MAXX(ALLSELECTED('Table'),[Date])
return
CALCULATE(
    DISTINCTCOUNT('Table'[Employee]),
FILTER(ALL('Table'),'Table'[Event]=MAX('Table'[Event])))

Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak · Accepted solution]

@tproost, I think you Dynamic segmentation, refer example

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ