Forum Discussion

SammuelM's avatar
SammuelM
Helper 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]
    )

     

     

SammuelM's avatar
SammuelM
Helper 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
    Helper 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