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"
    )
)
dufoq3's avatar
dufoq3
Community 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
    Community 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 🙂