Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 Consu...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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