DAX, M, maybe R? help please

[bvilten]

Hello All,

I posted a similar question but was not able to explaine the issue clearly enough so I am starting fresh. I thought perhaps describing the problem in terms of the VBA solution might work.

In VBA I read through a table to get a list of unique UserID, Then one unique UserID at a time I search through the table and put all records into a collection then I run the following if statements with the intent that only one record per loop through the collection is copied to a new sheet. I seems like I can do this with nested if statements but I can not make it work.

'Add resource as Active
For i = 1 To RngCount
    If Sheet2.Cells(i, 3).Offset(0, 0).Value >= Date Then
        LastRowUsed = Sheets("Active Resource Report").Range("A" & Rows.Count).End(xlUp).Row + 1
        Sheet2.Range("A" & i).EntireRow.Copy Sheets("Active Resource Report").Range("A" & LastRowUsed)
        Exit Sub
    End If
Next i

'Discard resource as properly offboarded
For i = 1 To RngCount
'Debug.Print Sheet2.Cells(i, 3).Offset(0, 0).Value
    If Sheet2.Cells(i, 3).Offset(0, 0).Value < Date Then
        If Trim(Sheet2.Cells(i, 5).Value) = "Offboard" Or Sheet2.Cells(i, 4).Value <> AED Then Exit Sub
    End If
Next i

'Add resource as possibly not offboarded
For i = 1 To RngCount
    If Sheet2.Cells(i, 3).Offset(0, 0).Value < Date Then
        If Sheet2.Cells(i, 5).Value = "Contract Extension" Or Sheet2.Cells(i, 5).Value = "Onboard" Then
                LastRowUsed = Sheets("Expired Resource Report").Range("A" & Rows.Count).End(xlUp).Row + 1
                Sheet2.Range("A" & i).EntireRow.Copy Sheets("Expired Resource Report").Range("A" & LastRowUsed)
                Exit Sub
        End If
    End If
Next i

The last post to my previous question https://community.powerbi.com/t5/Desktop/Need-help-with-DAX-formula/td-p/668053 contains sample data before and after.

[Zubair_Muhammad · Accepted solution]

@bvilten 

Please check the "M" Solution as well

Table2 in the attached file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZA9C4NAEET/y9XCeJ/e1cHaLo1YGIwghChW+fmOIYF4Ss6tlrePWZi6Fv49IhMSEiqXgWv1vI3t3HErX9Mw3zvRZB9zPSuotBlyQg39NS/lkSQJDcyJOEVoYffmtX0MP54mcnCbt5FiiAoUyShL5OGTniMKCClv13Lfn635uLyo4T95cc/bvGYB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UserID = _t, LCED = _t, ACTION = _t, EXPIRED = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", Int64.Type}, {"LCED", type date}, {"ACTION", type text}, {"EXPIRED", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"UserID"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Contains([All][EXPIRED],"Expired") and List.Contains([All][ACTION],"Offboard")=false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true))
in
    #"Filtered Rows"

[Zubair_Muhammad]

Hi @bvilten 

Try this calculated table.

From Modelling tab>>New Table

Please see attached file as well

Calculated Table =
VAR CheckExpired =
    FILTER (
        VALUES ( Table1[UserID] ),
        CALCULATE ( COUNTROWS ( Table1 ), Table1[EXPIRED] = "Expired" ) > 0
    )
VAR CheckOffboard =
    EXCEPT (
        VALUES ( Table1[UserID] ),
        FILTER (
            VALUES ( Table1[UserID] ),
            CALCULATE ( COUNTROWS ( Table1 ), Table1[ACTION] = "Offboard" ) > 0
        )
    )
RETURN
    INTERSECT ( CheckExpired, CheckOffboard )