Forum Discussion
Aggregating / counting records with DAX using conditions
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" ) )
Community ChampionHi 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")
)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.
