Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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

IDSubmission recordOutput

Karl

SubmittedSubmitted
KarlSubmittedSubmitted
JohnNot SubmittedNot Submitted
JohnNot SubmittedNot Submitted
PeterSubmittedSubmitted
PeterNot in ScopeSubmitted

 

 

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]

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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"
  )

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!