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
Raphiraf
Regular Visitor

Relationships not working

hello community, 

i need help because i am stuck with a table not responding to my request: 

i have three colonnes.
From the same table but impossible to find what I want : 

Project Name

Consume workload 

distinct number of Authors 

only 1 filter from a slicer : date week for a date master table.

The problem it's the table show me only one project and there author. 
I'm from the table view I can see all the infos. I don't understand why not on the report view. 
thx for your help

 

4 REPLIES 4
BeaBF
Memorable Member
Memorable Member

@Raphiraf Can you share the pbix file?

 

BBF

Hello @BeaBF ,

I apologize, but I can't share the data as it is sensitive.
However, I can share my formulas with you if that helps.

@Raphiraf Yes, shares formulas and if you can a sample of datas on which you calculate those formulas.

In particular, those datas that you should see on visuals but don't return.

 

One question, the date table has a relationship with the fact table?

 

BBF

Context: We faced challenges in correctly filtering data in a specific visual of our Power BI report, particularly in calculating the distinct count of authors by project and week using a date slicer.

Formulas Used

  1. Consumed Workload:

    Consumed Workload =
    CALCULATE(
    SUM('Worklogs'[Division]),
    REMOVEFILTERS('Date Master'[Week])
    )

Purpose: To calculate the sum of divisions from worklogs without being affected by the Date Master week filter.

 

Distinct Authors by Week and Project:

Distinct Authors by Week and Project =
CALCULATE(
DISTINCTCOUNT(Worklogs[AUTHOR_NAME]),
TREATAS(VALUES('Date Master'[Week]), Worklogs[Week]),
TREATAS(VALUES('Date Master'[Year]), Worklogs[Year])
)

Purpose: To calculate the distinct count of authors by project and week by applying Date Master filters to Worklogs.

 

Table Relationships

  1. Projects (PROJECT_ID) to Issues (PROJECT_ID):

    • Type: One-to-Many
    • Active relationship, essential for linking projects to issues.
  2. Issues (ISSUE_ID) to Worklogs (ISSUE_ID):

    • Type: One-to-Many
    • Active relationship, necessary for connecting issues to worklogs.
  3. Date Master (Week) to Worklogs (Week):

    • Type: One-to-Many
    • Active relationship, enabling filtering of worklogs by week through Date Master.

Problems Encountered

  • Filter Interference: Using the Date Master[Week] slicer for all visuals resulted in incorrect data in the project table because it wasn't directly connected to Worklogs.
  • Complex Relationships: Attempts to enforce relationships and filters through DAX measures did not yield the expected results.

Solution Implemented

To resolve this issue, I decided to use two distinct date slicers:

  1. Worklogs[Week] Slicer: Used only for the table displaying project names, Consumed Workload, and Weekly Resources.
  2. Date Master[Week] Slicer: Used for all other visuals in the report.
  1. Consumed Workload:

    dax
     

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.