Issue with DAX: getting a score for a team in a specific week.

Anonymous — Sat, 28 Jan 2023 10:22:16 GMT

Hi fellow Power BI enthousiasts,

Introduction

I'm working a power BI dashboard that monitors the performance of teams on different scores for every week for an organisation I'm working for.

Data

At unregelar moments I receive new data for a specfic week for all teams. This can be once a month, but also twice. The below table displays the data I'm receiving:

Account	Project	Comment	Weeknumber	GeneralScore*
A	Project A	Good performance	2	3
A	Project B	Working hard	2	1
B	Project C	Getting better	1	8
B	Project D	Not yet on full capacity	1	6

* GeneralScore is a score of team performance that is 1, 2, or 3.

I want to display this data in the following table in PBI:

Account	Project	Comment	Weeknumber	GeneralScoreT-2**
A	Project A	Good performance	2	3
A	Project B	Working hard	2	1
B	Project C	Getting better	1	***
B	Project D	Not yet on full capacity	1	***

Issue

** Is the GeneralScore of a team on CURRENTWEEK-2. So currently we are in week 4, which will means that the column is displaying the GeneralScores for all teams on week 2.

***If there is no data on CURRENTWEEK-2 for an project, it is important the the row is still displayed, only that cel is then empty.

I tried to create different DAX measures to get this working. However, I'm having issues with the rows that have no data for a specific week, they are not displayed in the table.

DAX measure that I tried

Option 1

General-2 = CALCULATE ( MAX ( 'Delivery data'[GeneralScore] ), ALLEXCEPT ( 'table', 'Delivery data'[Account], 'Delivery data'[Project], 'Delivery data'[Comment]), 'Delivery data'[Weeknumber] = WEEKNUM(TODAY(), 21) -2 )

Option 2

General-2 = CALCULATE ( MAX( 'table'[GeneralScore] ), ALLEXCEPT ( 'table', 'table'[Project]), 'table'[Weeknumber] = WEEKNUM(TODAY(), 21) - 2 )

Both options have the same issue: no rows are displayed if there is no data on week CURRENTWEEK-2 for a team.

Could some please help with this issue? I'm really curious what I'm doing wrong. Please let me know if you need additional information.

Thanks in advance!

Re: Issue with DAX: getting a score for a team in a specific week.

ERD — Mon, 30 Jan 2023 07:39:57 GMT

Hi Anonymous ,

You can use Show items with no data feature: Show items with no data in Power BI - Power BI | Microsoft Learn

topic Re: Issue with DAX: getting a score for a team in a specific week. in DAX Commands and Tips

Issue with DAX: getting a score for a team in a specific week.

Re: Issue with DAX: getting a score for a team in a specific week.