Hello everyone,
I'm facing a challenge in Power BI that might be a bit unconventional, and I'm hoping to tap into the collective wisdom of this community for guidance. I'm fairly new to PBI, and have hit this major roadblock in my current project.
Challenge Description:
I'm working on creating a table visual in my report to display monthly completions by department. However, the completion item "assessment" has unique requirements. Some departments have a "target" designation, requiring monthly completion, while others, labeled "non-target," only need to complete the assessment once every quarter.
Goal:
I aim to show monthly completions, including dates, for all departments. For non-target locations, the same completion date should appear three times across the quarter it was completed. For example, if Department 1234 completes the assessment on Mar 7, the February, March, and April rows should all show "Mar 7" in the Assessment column.
Data Structure:
Each completion topic (Assessment, Test,Etc) has its own dataset.
There's an alignment file with each department, which has a 1:* relationship with each completion topic table, as each of those have a department column with each assessment/test submission. This alignment file with each department in one column also shows the target/nontarget designation for each department in another column.
A calendar table contains individual calendar dates, fiscal months, quarters, and years. There are 1:* relationships with the submission dates, such as our culprit: Assessment Date.
Current Challenge:
The month column from the fiscal table is causing only the earliest date to show up in the visual, and I'm seeking a solution to essentially make one column ignore part of a relationship.
The visual currently shows: (This is a snippet of what I’m trying to create, there are more than just assessment and test, but test and all other requirements except for Assessment have only monthly requirements) Target Store will not be included in the visual, I've just included it for you to see: target store is in the alignment file.
Department | Month | Assessment | Test | Target Store |
1234 | Feb | 6-Feb | 5-Feb | Non-Target |
1234 | Mar | | 6-Mar | Non-Target |
1234 | Apr | | 9-Apr | Non-Target |
1234 | May | | 9-May | Non-Target |
1234 | Jun | 9-Jun | 1-Jun | Non-Target |
1234 | Jul | | 5-Jul | Non-Target |
4321 | Feb | 20-Feb | 3-Feb | Target |
4321 | Mar | 5-Mar | 7-Mar | Target |
4321 | Apr | 12-Apr | 2-Apr | Target |
4321 | May | 7-May | 7-May | Target |
4321 | Jun | 9-Jun | 2-Jun | Target |
4321 | Jul | 3-Jul | 19-Mar | Target |
And I have the goal of it showing:
Department | Month | Assessment | Test | Target Store |
1234 | Feb | 6-Feb | 5-Feb | Non-Target |
1234 | Mar | 6-Feb | 6-Mar | Non-Target |
1234 | Apr | 6-Feb | 9-Apr | Non-Target |
1234 | May | 9-Jun | 9-May | Non-Target |
1234 | Jun | 9-Jun | 1-Jun | Non-Target |
1234 | Jul | 9-Jun | 5-Jul | Non-Target |
4321 | Feb | 20-Feb | 3-Feb | Target |
4321 | Mar | 5-Mar | 7-Mar | Target |
4321 | Apr | 12-Apr | 2-Apr | Target |
4321 | May | 7-May | 7-May | Target |
4321 | Jun | 9-Jun | 2-Jun | Target |
4321 | Jul | 3-Jul | 19-Mar | Target |
To clarify: Target is displaying correctly by using Earliest of Assessment Date in the visual. But the Assessment Dates for Non-Target departments I want to show in the other months in the same quarter as specified by the related fiscal table.
I hope this provides a clear picture of the issue. Feel free to ask any clarifying questions or share any insights you may have.
Thank you for your time and assistance!
