Forum Discussion

pplanch's avatar
pplanch
Regular Visitor
2 years ago
Solved

Aggregating / counting records with DAX using conditions

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

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

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

6 Replies

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      pplanch
      Regular Visitor
      2 years ago

      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's avatar
        dufoq3
        Icon for Community Champion rankCommunity Champion
        2 years ago

        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.