How to get the Max unique value for a particular person from two different columns

[Mavericks334]

Hi,

I have 3 columns { Preparer Name, REviewer NAme, Completed Date}
I need to get the max no of days where person has worked, by counting unique dates from completed date.

i am able to get the unique dates by using COuntsrows(Distinct(Completed date)).

However i need to get the max days where the person has worked, the person could either be in preparer or reviewer or in both.

REgards,
Renato.

 

[Fazza]

hi, Renato. In normal Excel, I don't have Power Pivot, try below. Untested. HTH

SELECT Name, COUNT(*)
FROM
(SELECT DISTINCT Name, INT(Completed Date)
FROM
(
SELECT [Preparer Name] AS [Name], [Completed Date]
FROM table
UNION
SELECT [Reveiwer Name] AS [Name], [Completed Date}
FROM table))
GROUP BY Name

 

[Fazza]

Sorry, Renato. I have some typos & can't edit them. Browser keeps crashing. Please use the following instead, there were errors previously.

SELECT Name, COUNT(*)
FROM
(SELECT DISTINCT Name, INT(Completed Date)
FROM
(
SELECT [Preparer Name] AS [Name], [Completed Date]
FROM table
UNION
SELECT [Reviewer Name] AS [Name], [Completed Date]
FROM table))
GROUP BY Name

 

[Mavericks334]

Hi Fazza,

Thanks for the help.

If you could provide me the solution for power pivot would be great.

I am currently using =CALCULATE(COUNTROWS(DISTINCT('SharePoint Data'[Uniquedate])),ALL('SharePoint Data'[Preparer]))
To get the unique values for the preparer, however when i try and use reviewer at the end, it still calculates the values for preparer, i dont know why.

 

[Laurent C]

Hi Fazza,

Thanks for the help.

If you could provide me the solution for power pivot would be great.

I am currently using =CALCULATE(COUNTROWS(DISTINCT('SharePoint Data'[Uniquedate])),ALL('SharePoint Data'[Preparer]))
To get the unique values for the preparer, however when i try and use reviewer at the end, it still calculates the values for preparer, i dont know why.

To get the data for reviewers, you will have to use the inactive relationship (with USERELATIONSHIP).

In your case, you will have to use a FILTER expression on your 'Sharepoint Data' table, with a condition using both your active relationship and your inactive relationship.

 

[Mavericks334]

To get the data for reviewers, you will have to use the inactive relationship (with USERELATIONSHIP).

In your case, you will have to use a FILTER expression on your 'Sharepoint Data' table, with a condition using both your active relationship and your inactive relationship.

Hi Laurent,

I tried the below expression and it did not work,

=(COUNTROWS(DISTINCT('SharePoint Data'[Uniquedate])),USERELATIONSHIP('SharePoint Data'[Reviewer]))

Could you let me know, what changes i need to make.

 

[Mavericks334]

=CALCULATE(COUNTROWS(DISTINCT('SharePoint Data'[Uniquedate])),USERELATIONSHIP('SharePoint Data'[Reviewer],'Employee Details'[Employee Name]),'SharePoint Data'[Recon Category]="Cash")

It worked