Aggregating / counting records with DAX using conditions

[pplanch]

I have a table showing below.

What I need to do through DAX (measure ... I don't have access to make changes to the underlying data source) is produce a column with a total count for each name - "Naam" - where "Reden" equals "Hoofdperiode". So for every record for "Jan" it should show 4 except where "Reden" shows "Vervolg", for every record for "Mieke" it should show 2 except where "Reden" shows "Vervolg".

I'm not finding the right DAX functions / formula to use ... anybody has some suggestions?

Thanks

[dufoq3]

Hi @pplanch,

Power Query version:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMgRiIyD2yM9PSylILcrMT0lVitVBlw5LLSrLz0nHIkNAI6nSvpmp2amkKEB2GX7NsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Naam = _t, Start = _t, Einde = _t, Reden = _t]),
    GroupedRows = Table.Group(Source, {"Naam"}, {{"All", each _, type table}, {"Hoofdperiode Count", each Table.RowCount(Table.SelectRows(_, (x)=> x[Reden] = "Hoofdperiode")), Int16.Type}}),
    ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", {"Start", "Einde", "Reden"}, {"Start", "Einde", "Reden"})
in
    ExpandedAll

If you want Dax Measure, create new calculated column wit if:

Hoofperiode Count = IF([Reden] = "Hoofdperiode", 1, 0)

And then just create sum measure for [Hoofperiode Count] column. Put [Naam] into rows.

or create direct measure:

Hoofperiode Count = 
CALCULATE(
    COUNTROWS(YourTable),
    KEEPFILTERS(YourTable[Reden] = "Hoofdperiode")
)

[pplanch]

@dufoq3, thanks for your suggestion. First step ... no problem. When creating the measure for the sum, the result seems to be the same as the one for the calculated column. Anything I'm dowing wrong? I tried with SUM but that didn't allow for putting any argument into Rows, screenshot below shows an attempt with SUMX

(these are my first steps in DAX)

[dufoq3]

I'm not sure what do you want to achieve.

Create this measure

Hoofperiode Count = 
CALCULATE(
    COUNTROWS(tblSicknessData),
    KEEPFILTERS(tblSicknessData[Reden] = "Hoofdperiode")
)

Put Naam into matrix rows and use this measure. It will show you count of all "Hoofdperiode" for every Naam.

[pplanch]

@dufoq3 Not sure how to "put Naam into matrix rows", as I am not yet familiar with the ins and outs of DAX. What I try to achieve, is showing in screenshot below (from Excel "simulation")

[dufoq3 · Accepted solution]

I did it for you in Power Query in post above, but if you want to create this in DAX here you go:

Hoofdperiode Count = 
CALCULATE(
    COUNTROWS(tblSicknessData),
    FILTER(
        ALLEXCEPT(tblSicknessData, tblSicknessData[Naam]),
        tblSicknessData[Reden] = "Hoofdperiode"
    )
)

[pplanch]

@dufoq3 Thanks alot. Since I don't have access to the data model, DAX was the only way ... solution appears to work like a charm 🙂