Count of Duplicates with multiple columns and then Distinct Count om ID

[RanjeetK]

Hello BI Community,
I have to count the number of duplicate records and then Distinct Count on Project ID so that I can get unique records -
please take a look of below table
I have done it by using report level filters But I want it in Measure (Not in Calculated Column or Table)
Input :

Output :

so I want to do it in Measure wich returns 1173 .
i use below measure to return above table - 

it gives me following error 

Please help me out 🤔
Thank you in Advance !!!!
Regards,
RK
PBIX File ----https://drive.google.com/drive/folders/1BFsxCEyM6iYqp6VXGJAlvllpgqSAr2Nx?usp=share_link 

[RanjeetK]

@amitchandak @Gaurav_Lakhotia 
Hello 
Did any one found solution 
please let me know..............................................

Regards,
RK

[RanjeetK]

Any Update anyone..............
in SQL we can retrive data from two tables using following Query

with Count_Of_Duplicate
as
(
select COUNT(PROJECT_STANDARD_ACCREDITATION.BK_NGPSBI_PROJECT_STANDARD_ACCREDITATION) AS COUNT_OF_BK,
Project.Project_ID,
PROJECT_STANDARD_ACCREDITATION.Project_Std_Acr_Accreditation_Id,
PROJECT_STANDARD_ACCREDITATION.Project_Std_Acr_Standard_Id
from PROJECT_STANDARD_ACCREDITATION
Full Outer Join Project
on PROJECT_STANDARD_ACCREDITATION.Project_ID=project.Project_ID
group by Project.Project_ID,
PROJECT_STANDARD_ACCREDITATION.Project_Std_Acr_Accreditation_Id,
PROJECT_STANDARD_ACCREDITATION.Project_Std_Acr_Standard_Id
HAVING COUNT(PROJECT_STANDARD_ACCREDITATION.BK_NGPSBI_PROJECT_STANDARD_ACCREDITATION) > 1
)
select DISTINCT [Project_ID] from Count_Of_Duplicate

Thanks & Regards,
Ranjeet 😊

Let me know....

[Gaurav_Lakhotia]

Hi @RanjeetK,

Need more clarity on the logic you have implemented.

There are total 7 line items for Project ID 10

In the below table your are showing "Count of BK_NGPSBI_PROJECT_STANDARD_ACCREDITATION" as 2. Not able to get the logic. Please clarify.

Regards,

Gaurav

[RanjeetK]

Hi @Gaurav_Lakhotia 
thanks for reply,

10416 is Project ID, there are three duplicate Acr ID and Stand, ID combination

(10416  133  32 ) but BK ID is diffrent for every record so grouping on Project ID , Acr ID & Stand. ID
1st req - i need to find all duplicate reords first 

PQ 1 = CALCULATE(COUNT('Table 1'[BK_NGPSBI_PROJECT_STANDARD_ACCREDITATION]))

Step 2 - PQ1 >1 condition so we can exclude single records

Step 3 - Now count distinct Project ID from table 2 only as i have this type of model and i want it in measure

PQ 2 = CALCULATE(COUNT('Table 1'[Project ID]),FILTER('Table 1',[PQ 1]>=2)) 
PQ2 return blank...........
so i try Summerizecolumn
Please reffer attached file (NEW) at below link
PBIX file - https://drive.google.com/drive/folders/1BFsxCEyM6iYqp6VXGJAlvllpgqSAr2Nx?usp=share_link 

Regards,
RK

[amitchandak]

@RanjeetK , try to use summarize in place of summarizecolumns and rebuild the measure

[RanjeetK]

Hi @amitchandak ,
Yes, Actually, I need to do Grouping on Project ID, Accreditation Id & Standard Id (Combination of these 3) and BK is unique, so counting on BK and grouping on Project ID, Accreditation Id & Standard Id  I use to summerize but gives me wrong ANS 5632

PQ 3 =

Var A = ADDCOLUMNS(SUMMARIZE('Table 3','Table 3'[Project ID]),

                                    "Count Of Duplicate",

                                     CALCULATE(COUNT('Table 1'[BK_NGPSBI_PROJECT_STANDARD_ACCREDITATION]))

                )

Var B = FILTER(A,[Count Of Duplicate]>=2)

Var C = COUNTROWS(B)

RETURN

C

But I want this

i want above image conditions in a Masure
Please reffer this PBIX file - New 
https://drive.google.com/drive/folders/1BFsxCEyM6iYqp6VXGJAlvllpgqSAr2Nx?usp=share_link 
please help me out

Regards,
RK