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...
BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

Anonymous Can you share the pbix file?

 

BBF

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