Need help getting ID from a column ( can have multiple same ID) and outputting all their data

[Anonymous]

Hey guys i dont know how to explain this but i do need something like this:

ID	Submission record	Output
Karl	Submitted	Submitted
Karl	Submitted	Submitted
John	Not Submitted	Not Submitted
John	Not Submitted	Not Submitted
Peter	Submitted	Submitted
Peter	Not in Scope	Submitted

so first is group the name and get their submission record so for Karl he does have 2 "Submitted" and here comes the if statements which is

if Submission records are Not Submitted then [Output]=Not Submitted

if Submission record Not in Scope then [Output]=Not in Scope

if Submission records contains Not in Scope and Submitted then [Output]=Submitted

if Submission records contains Submitted and Not Submitted then [Output]= Submitted 

i can do the if statements but i dont know how to group them by [ID] and check their [Submission] base on [ID]

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Create a column as below:

Column =
VAR _count1 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
VAR _countsubimtted =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Submission record] = "Submitted"
        )
    )
VAR _countnotsub =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Submission record] = "Not Submitted"
        )
    )
VAR _notinscope =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Submission record] = "Not in Scope"
        )
    )
RETURN
    IF (
        _count1 = _countnotsub,
        "Not Submitted",
        IF ( _count1 = _notinscope, "Not in Scope", "Submitted" )
    )

Or  a measure as below:

Measure =
VAR _count1 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) )
    )
VAR _countsubimtted =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = MAX ( 'Table'[ID] )
                && 'Table'[Submission record] = "Submitted"
        )
    )
VAR _countnotsub =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = MAX ( 'Table'[ID] )
                && 'Table'[Submission record] = "Not Submitted"
        )
    )
VAR _notinscope =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = MAX ( 'Table'[ID] )
                && 'Table'[Submission record] = "Not in Scope"
        )
    )
RETURN
    IF (
        _count1 = _countnotsub,
        "Not Submitted",
        IF ( _count1 = _notinscope, "Not in Scope", "Submitted" )
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Greg_Deckler]

@Anonymous Maybe:

Measure =
  VAR __ID = MAX('Table'[ID])
  VAR __Table = SELECTCOLUMNS('Table',"Submission",[Submission])
RETURN
  SWITCH(TRUE(),
    "Submitted" IN __Table,"Submitted",
    "Not Submitted" IN __Table,"Not Submitted",
    "Not in Scope"
  )