Distinct count if?

[rezboot]

I have data that is similar to the below - 

I need create a measure that counts the number of distinct outreaches were made per job filtered by OB Call, OB Call - LVM, and Prescreen.

For this example data the returned values should be Job 1 = 3 people, Job 2 = 2 people

Can someone please assist on what this would look like?

[lbendlin · Accepted solution]

[dufoq3 · Accepted solution]

Hi @rezboot,

DAX solution:

Output

Candidate DistinctCount = 
VAR _candidates = 
CALCULATE(
    DISTINCTCOUNT(Table1[Candidate]),
    KEEPFILTERS(Table1[Note Action] IN {"OB Call", "OB Call - LVM", "Prescreen"})
)

VAR _blankTotal = 
    IF(ISINSCOPE(Table1[Job #]), _candidates)

RETURN _blankTotal

Power Query Solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ndScE7MyVHSUfLKz8gDUoZKsTpwYQVdBZ8wX3TJgKLU4uSi1FSQiFNRZiJ2GefEoqLMVLiUa25iZg6SBiMUUaj5RjjNh8gE+wZjiGG10wjZF2gSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Note Action" = _t, Candidate = _t, #"Job #" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Job #", Int64.Type}}),
    FilteredRows = Table.SelectRows(ChangedType, each List.Contains({"OB Call", "OB Call - LVM", "Prescreen"}, [Note Action])),
    GroupedRows = Table.Group(FilteredRows, {"Job #"}, {{"All", each List.Count(List.Distinct([Candidate])), Int64.Type}})
in
    GroupedRows

[Omid_Motamedise · Accepted solution]

Hi,

Use this formula

let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Job #"}, {{"Number", each _}}),
#"Multiplied Column" = Table.TransformColumns(#"Grouped Rows", {{"Number", each List.Count(List.Distinct(Table.SelectRows(_, (x)=> x[Note Action]="OB Call" or [Note Action]="OB Call - LVM" or x[Note Action]="Prescreen" )[Candiate]))}})
in
#"Multiplied Column"

[rezboot]

Thanks! THis was very helpful and worked

[dufoq3]

You're welcome 😉

[dufoq3 · Accepted solution]

Hi @rezboot,

DAX solution:

Output

Candidate DistinctCount = 
VAR _candidates = 
CALCULATE(
    DISTINCTCOUNT(Table1[Candidate]),
    KEEPFILTERS(Table1[Note Action] IN {"OB Call", "OB Call - LVM", "Prescreen"})
)

VAR _blankTotal = 
    IF(ISINSCOPE(Table1[Job #]), _candidates)

RETURN _blankTotal

Power Query Solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ndScE7MyVHSUfLKz8gDUoZKsTpwYQVdBZ8wX3TJgKLU4uSi1FSQiFNRZiJ2GefEoqLMVLiUa25iZg6SBiMUUaj5RjjNh8gE+wZjiGG10wjZF2gSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Note Action" = _t, Candidate = _t, #"Job #" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Job #", Int64.Type}}),
    FilteredRows = Table.SelectRows(ChangedType, each List.Contains({"OB Call", "OB Call - LVM", "Prescreen"}, [Note Action])),
    GroupedRows = Table.Group(FilteredRows, {"Job #"}, {{"All", each List.Count(List.Distinct([Candidate])), Int64.Type}})
in
    GroupedRows

[lbendlin · Accepted solution]