topic Relationships not working in DAX Commands and Tips

Relationships not working

Anonymous — Sat, 13 Jul 2024 17:38:26 GMT

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

Re: Relationships not working

BeaBF — Mon, 15 Jul 2024 07:16:48 GMT

Anonymous Can you share the pbix file?

BBF

Re: Relationships not working

Anonymous — Mon, 15 Jul 2024 07:50:27 GMT

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.

Re: Relationships not working

BeaBF — Mon, 15 Jul 2024 07:59:26 GMT

Anonymous 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

Re: Relationships not working

Anonymous — Mon, 15 Jul 2024 08:01:23 GMT

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

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

Projects (PROJECT_ID) to Issues (PROJECT_ID):
- Type: One-to-Many
- Active relationship, essential for linking projects to issues.
Issues (ISSUE_ID) to Worklogs (ISSUE_ID):
- Type: One-to-Many
- Active relationship, necessary for connecting issues to worklogs.
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:

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

Consumed Workload:
dax