Distinct Count of Value by Count of ID

[Sarath5c8]

I have an issue, it appeared simple but not getting the desired output. Below mentioned table should be in such a way that One ID is assigned to One Name i.e 1-1 mapping. Anything else is an invalid scenario.My goal is to tell what is the count of invalid scenarios present. 
REQUIRED OUTPUT is FLAG of 1 if invalid and 0 for Valid so that i can take sum of all the invalid
In the below table there are two invalid scenarios ie. one name - multiple ids (ABC is assigned to 01,02) and one id - multiple names (SDF & SDT - 03). 
I tried this but i feel there is a missing piece in it. 

1 Name|* ID =
var d=CALCULATE(MIN(tbl[ID]),ALLEXCEPT(tbl,tbl[NAME]))

return IF(tbl[ID]<>d,1,0)

Name	Id
ABC	01
DEF	02
ABC	02
SDF	03
SDT	03

[Samarth_18 · Accepted solution]

Hi @Sarath5c8 ,

You can create a column like below:-

Column = 
var correct_value=CALCULATE(MIN('Table (2)'[Id]),FILTER('Table (2)','Table (2)'[Name] = EARLIER('Table (2)'[Name])))

return if(correct_value = [Id],0,1)

Output:-

Thanks,

Samarth

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I think, in this sample, all of them are invalid cases.

ABC -> two ids

03 -> two names

02 -> two names

Please check the below picture and the attached pbix file.

One Name One ID Invalid check CC =
VAR currentname = tbl[Name]
VAR currentid = tbl[Id]
VAR currentnametable =
    SUMMARIZE ( FILTER ( tbl, tbl[Name] = currentname ), tbl[Id] )
VAR currentidtable =
    SUMMARIZE ( FILTER ( tbl, tbl[Id] = currentid ), tbl[Name] )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( currentnametable ) <> 1, 1,
        COUNTROWS ( currentidtable ) <> 1, 1,
        0
    )

[Samarth_18 · Accepted solution]

Hi @Sarath5c8 ,

You can create a column like below:-

Column = 
var correct_value=CALCULATE(MIN('Table (2)'[Id]),FILTER('Table (2)','Table (2)'[Name] = EARLIER('Table (2)'[Name])))

return if(correct_value = [Id],0,1)

Output:-

Thanks,

Samarth