Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ragimoc
New Member

Table Visualization; Ignoring part of a relationship?

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!

1 ACCEPTED SOLUTION
Ragimoc
New Member

Solution Found: Firstly, I duplicated the query and established an additional relationship between the 'Quarter' in the dates table and the duplicated 'Quarter' in the assessment table. Next, I introduced a measure to the visual column using an IF function. This function considers the selected value from the first table for 'Target' and the selected value from the duplicated table (with the extra quarter relationship) for 'Non-Target.' As a result, the visualization now displays either one date or is empty for 'Target,' and for 'Non-Target,' it showcases the same date three times across all months in a quarter or remains empty, achieving the desired outcome.

 
 

View solution in original post

2 REPLIES 2
Ragimoc
New Member

Solution Found: Firstly, I duplicated the query and established an additional relationship between the 'Quarter' in the dates table and the duplicated 'Quarter' in the assessment table. Next, I introduced a measure to the visual column using an IF function. This function considers the selected value from the first table for 'Target' and the selected value from the duplicated table (with the extra quarter relationship) for 'Non-Target.' As a result, the visualization now displays either one date or is empty for 'Target,' and for 'Non-Target,' it showcases the same date three times across all months in a quarter or remains empty, achieving the desired outcome.

 
 
lbendlin
Super User
Super User

Are you maybe looking for the "Fill Down"  feature in Power Query?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.