Forum Discussion
Get Distinct Day per Week
Looking for ways to get # of distinct day of the week per month per year.
An example below in column # of Distinct Day is what I want to get.
It's a little difficult to explain but I hope that make sense.
Year | Month | Day of Week | # of Distinct Day |
| 2020 | Jan | Mon | 4 |
2020 | Jan | Tue | 4 |
| 2020 | Jan | Wed | 5 |
| 2020 | Jan | Thur | 5 |
| 2020 | Jan | Fri | 5 |
Hi atp015 ,
If you have a Calendar table in place, you could easily let Power BI do the grouping in a table visual.
Here a Calendar / Date table :
And here the viusal:
Otherwise, you could also create a calculated table on the base of the calendar table:
SUMMARIZE ( 'Table', 'Table'[Year], 'Table'[Month], 'Table'[Weekday Name], "# of Distinct Day", COUNT ( 'Table'[Weekday Name] ) )
As you can see in both cases the Calendar table does the trick. Generally, it is a best practice to have a Calendar table in your data model. Here a resource on how to create such one in DAX.
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
1 Reply
Hi atp015 ,
If you have a Calendar table in place, you could easily let Power BI do the grouping in a table visual.
Here a Calendar / Date table :
And here the viusal:
Otherwise, you could also create a calculated table on the base of the calendar table:
SUMMARIZE ( 'Table', 'Table'[Year], 'Table'[Month], 'Table'[Weekday Name], "# of Distinct Day", COUNT ( 'Table'[Weekday Name] ) )
As you can see in both cases the Calendar table does the trick. Generally, it is a best practice to have a Calendar table in your data model. Here a resource on how to create such one in DAX.
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/