Forum Discussion

SammuelM's avatar
SammuelM
Icon for Helper I rankHelper I
2 years ago
Solved

How do I write a Dax statement that returns the current counts whenever I filter for a TEam

Below is my sample table and I'd like to write a Dax statement that returns the current count whenever I filter for Team A or B. Ideally, the current count per team would be on a card in power BI. ...
Need Help
Show and Tell
  • SammuelM's avatar
    SammuelM
    2 years ago

    Solution. Thank you all.

     

    CurrentCountPerTeam =
    VAR __CurrentYearMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
    RETURN
    SUMX(
    FILTER(
    'Query1',
    'Query1'[cyearmonth] = __CurrentYearMonth
    ),
    'Query1'[active_cnt]
    )

     

     

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago

Try this to see how it is.

RecuentoActualPorEquipo =
CALCULATE(
SUM('Consulta1'[active_cnt]),
FILTER(
'Consulta1',
'Consulta1'[Equipo] = "a" || 'Consulta1'[Equipo] = "b"
),
LASTNONBLANK('Consulta1'[añomes], 1)
)

  • SammuelM's avatar
    SammuelM
    Icon for Helper I rankHelper I
    2 years ago

    pacomase1 Thank you but Can I get an english translation please?

    • pacomase1's avatar
      pacomase1
      Frequent Visitor
      2 years ago

      CurrentCountPerTeam =
      SUM('Query1'[active_cnt]),
      FILTER(
      'Query1',
      'Query1'[team] = "a" || 'Query1'[team ] = "b"
      ),
      LASTNONBLANK('Query1'[yearmonth], 1)
      )

      • SammuelM's avatar
        SammuelM
        Icon for Helper I rankHelper I
        2 years ago

        Thank you. But I have multiples teams(roughly 40). How do I account for all 40 instead of just 'a' & 'b'. The data you in my original post was just a sample

  • SammuelM's avatar
    SammuelM
    Icon for Helper I rankHelper I
    2 years ago

    Solution. Thank you all.

     

    CurrentCountPerTeam =
    VAR __CurrentYearMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
    RETURN
    SUMX(
    FILTER(
    'Query1',
    'Query1'[cyearmonth] = __CurrentYearMonth
    ),
    'Query1'[active_cnt]
    )