Forum Discussion

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

DAX Calculate Countrows, average per Year per Weekday

Hello,   I think I have a simple question but I can't figure out how to do it.   I have a table in BI for example with a Date column and a column with the matching weekdays. There can be multiple...
COUNTROWS
dates
DAX
Weekday
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    Hi bkoenen ,

     

    How about this:

     

     

    Here the measure:

    Average Countrows per Year per Weekday = 
VAR _helpTable = 
SUMMARIZE (
    Table,
    Table[Day of the week],
    "NumberOfDayOfTheWeek", COUNT ( Table[Day of the week] ),
    "DistinctNumberOfDates", DISTINCTCOUNT ( Table[Date] )
) 
RETURN
MAXX (_helpTable, [NumberOfDayOfTheWeek] ) / MAXX (_helpTable, [DistinctNumberOfDates] )

     

    And here how the helpTable would look like:

     

    Let me know if this helps or if you have any questions 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

     

  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, bkoenen 

     

    You can try the following methods.

    Measure:

    Result = 
VAR _N1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            [Day of the week] = SELECTEDVALUE ( 'Table'[Day of the week] )
        )
    )
VAR _N2 =
    CALCULATE (
        COUNT ( 'Table'[Day of the week] ),
        FILTER (
            ALL ( 'Table' ),
            [Day of the week] = SELECTEDVALUE ( 'Table'[Day of the week] )
        )
    )
RETURN
    DIVIDE ( _N2, _N1 )

     

    COUNT: https://docs.microsoft.com/dax/count-function-dax 

    DISTINCTCOUNT: https://docs.microsoft.com/dax/distinctcount-function-dax 

     

    Best Regards,

    Community Support Team _Charlotte

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

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
4 years ago

Change to this:

[Your Column] = // a calc column, not a measure
var thisYear = year( T[Date] )
var dayOfWeek = T[Day of Week]
var RowsOfInterest =
    filter(
        T,
        year( T[Date] ) = thisYear
        &&
        T[Day Of Week] = dayOfWeek
    )    
var numOfSameWeekdays = countrows( RowsOfInterest )
var numOfDifferentDates =
    countrows(
        distinct(
            selectcolumns(
                RowsOfInterest,
                "@Date", T[Date]
            )
        )
    )
var ratio = numOfSameWeekdays / numOfDifferentDates
return
    ratio
bkoenen's avatar
bkoenen
Icon for Helper I rankHelper I
4 years ago

Hello,

 

I did a test with a smaller amount of rows(20000). There are 2 dates on a Monday in the DATUM column, I took as return result only the VAR "numOfDifferentDates" to see the output. This gives 1704 and should be 2(Mondays) if we got this one correct and we put back the formula then it would be correct.