Forum Discussion

sandy2786's avatar
sandy2786
New Member
3 years ago
Solved

Need help converting DAX to M code

I cannot figure out how to convert the following DAX to M code.  I need to get the count of each Weekday by Month

11/2022  Sunday   4

11/2022 Monday  4

11/2022 Tuesday 5

etc.

 

=VAR MonthYear = Dates[MonthnYear]
VAR __Weekday = Dates[Day of Week]
return
CALCULATE(
COUNTROWS (Dates),
FILTER ( Dates,
Dates[MonthnYear] = MonthYear
&& Dates[Day of Week] = __Weekday
) )

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi sandy2786 ,

    According to your description, I create a sample. Based on the code you provided I create a calculated column and get the following result:

    If you want to do it in M language, here's my solution. Create a custom column.

    Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[MonthnYear]=[MonthnYear]and x[Day of Week]=[Day of Week]))

    Get the same result:

    In addition, if you just want to show the result in a visual, you can simply put Date column in the visual and select Count without other calculation.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • speedramps's avatar
    speedramps
    Super User
    3 years ago

    Dont use DAx for date logic,  use calendar table.

    Build a good calamndar tabnbel onace and use it always.

    Click here to learn about Power Bi calendar tables 

     

    You can quickly do it like this

    Click Modelling and New Table

    Calendar = CALENDAR(DATE(2020,01,01),DATE(2023,12,31))
    Click New column
    Period =
    FORMAT('Calendar'[Date],"mmm") & " " & FORMAT('Calendar'[Date],"yyyy")
     
    Click New column
    Day name = FORMAT('Calendar'[Date],"dddd")
     
    Click New column
    Weekdays = IF(NOT WEEKDAY('Calendar'[Date],2) IN {6,7},1)
     
    then build your report ...
     

     

     

     

     
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi sandy2786 ,

    According to your description, I create a sample. Based on the code you provided I create a calculated column and get the following result:

    If you want to do it in M language, here's my solution. Create a custom column.

    Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[MonthnYear]=[MonthnYear]and x[Day of Week]=[Day of Week]))

    Get the same result:

    In addition, if you just want to show the result in a visual, you can simply put Date column in the visual and select Count without other calculation.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.