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

  • pplanch's avatar
    pplanch
    Regular Visitor
    2 years ago

    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's avatar
      dufoq3
      Icon for Community Champion rankCommunity Champion
      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"
    )
)
      • pplanch's avatar
        pplanch
        Regular Visitor
        2 years ago

        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 🙂