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

     

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
2 years ago

SammuelM seems like yearmonth column in your table is stored as a text, if that is the case then do this:

 

CurrentCountPerTeam =
VAR __Current = FORMAT ( TODAY (), "YYYY-MM" )
RETURN
SUMX(
FILTER(
'Query1',
'Query1'[yearmonth] = __Current
),
'Query1'[active_cnt]
)
SammuelM's avatar
SammuelM
Icon for Helper I rankHelper I
2 years ago