Power Pivot - Occurrence Query

[gmazza76]

Good Afternoon

I am trying to complete a query "Count" of how many times a holiday has been booked on a day and I am having a couple of issues.
As per the screenshot below I am trying to confirm how many times I have entered a holiday, but the holidays are shown as separate days on the file I am currently using in Power Pivot.
I need to be able to cross reference the "Entered Date" with the "Name" to confirm how many have been completed on the day.

Currently anything I think of would register the "Me" as having entered 5 separate entries on the 01/11/20 where I would only enter 1.
I am unsure of the best way of counting the below.

All help appreciated.
thanks
Gavin

 

[sandy666]

any expected result ?
use XL2BB to post example

 

[gmazza76]

Afternoon @sandy666,

Ideally I want to be able to use the Count column as my Values in a Pivot Table.
Currently I have tried to Rank the order to give me an outcome, but if the same date appears matching the name it leaves an outcome of "2" and so on.

I am unsure how to do an "if" which I could do in Excel, but there are huge amounts of lines I am trying to calculate

thanks

 

[sandy666]

I'll repeat

any expected result ?
use XL2BB to post example

 

[gmazza76]

Sorry I am unsure what you mean.

Based on the pic above the result should be the outcome in the count column

 

[gmazza76]

ie 5 holiday requests showing but authorised on the same day should equal a 1 if the "Name" is the same.
so the top line should equal a 1 and the 4 underneath a blank or anything so in my results for the pivot I can include the 1 outcome only

 

[sandy666]

like this? (on the right)

 

[gmazza76]

that's it, but I am unsure of how to write this in DAX in 1 column.

 

[sandy666]

Why DAX?
use standard Insert - Pivot Table

 

[gmazza76]

As the data is 1 part of a Table in PowerPivot.
The data in Power Pivot does various calculations and has formulas that identify a specific part of the data that needs to be the Values part of the table.
Currently when I pull the data directly from Power Pivot into the pivot table it counts each occurrence above as multiples. so the 5 lines above are showing as 5 and should only show as 1

So I need the calculation if possible to show in PowerPivot so I can use the "Count" as my calculation field

Hopefully that makes sense as I am not the best at explaining things