Forum Discussion

PaulBoden's avatar
PaulBoden
Icon for Helper I rankHelper I
4 years ago
Solved

FOREACH / WHERE Statement in DAX

Hi All,   Feels like this should be straight-forward but I'm struggling with how to apply a calculation to sub-groups in my table without a WHERE statement.    I'm trying to identify how many wee...
Need Help
  • AilleryO's avatar
    AilleryO
    4 years ago

    Hi,

     

    In DAX it could be straightforward :

    If you make a table with YearMonth (in lines) and Week number
    with a COUNT or DISTINCTCOUNT as calculation, you'll get it.

     

    Or a DAX measure could be something like :

    # of Week = CALCULATE( DISTINCTCOUNT( MonCalendrier[NumSemISO] ) , 
ALLEXCEPT( MonCalendrier , MonCalendrier[Annee-Mois] ) )

     

    Tell us if it works and mark it as solved or tell us more about your issue.

     

  • PaulBoden's avatar
    PaulBoden
    4 years ago

    Yes, not sure how I missed it - suspect I was overcomplicating things 🙂 - but the below seems to have worked:

     

    Weeks in Month = CALCULATE(DISTINCTCOUNT([Week of Month]), ALLEXCEPT('DateMaster', DateMaster[Fiscal YearMonth]))
AilleryO's avatar
AilleryO
Icon for Memorable Member rankMemorable Member
4 years ago

Hi,

 

In DAX it could be straightforward :

If you make a table with YearMonth (in lines) and Week number
with a COUNT or DISTINCTCOUNT as calculation, you'll get it.

 

Or a DAX measure could be something like :

# of Week = CALCULATE( DISTINCTCOUNT( MonCalendrier[NumSemISO] ) , 
ALLEXCEPT( MonCalendrier , MonCalendrier[Annee-Mois] ) )

 

Tell us if it works and mark it as solved or tell us more about your issue.

 

  • PaulBoden's avatar
    PaulBoden
    Icon for Helper I rankHelper I
    4 years ago

    Yes, not sure how I missed it - suspect I was overcomplicating things 🙂 - but the below seems to have worked:

     

    Weeks in Month = CALCULATE(DISTINCTCOUNT([Week of Month]), ALLEXCEPT('DateMaster', DateMaster[Fiscal YearMonth]))