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

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

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 )