Forum Discussion

KW123's avatar
KW123
Helper V
1 year ago
Solved

3 Table Relationships

Hi there,  I am building a report which has the following tables: -Dates -Senior Employee goals -Middle Employee goals -Entry Employee goals -Employee list  In my visualization, I have thr...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi KW123 ,

    You did not provide your data model, so I tried to create several tables myself. However, because you involve too many tables, I am not sure whether the sample data I created is consistent with your actual situation. If it is not consistent or my solution is not helpful to you, please provide sample data in all your tables, thank you!
    Here are my sample datas:

    Here are the relationships:

    Then use these DAXs to create measures:

    Senior MTD Goal = 
CALCULATE (
    SUM ( 'Senior Employee Goals'[Goal 1] ),
    FILTER (
        ALLSELECTED ( 'Senior Employee Goals'[Date] ),
        'Senior Employee Goals'[Date] <= MAX ( 'Dates'[Date] ) &&
        MONTH ( 'Senior Employee Goals'[Date] ) = MONTH ( MAX ( 'Dates'[Date] ) ) &&
        YEAR ( 'Senior Employee Goals'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) )
    )
)
    Mid MTD Goal = 
CALCULATE (
    SUM ( 'Middle Employee Goals'[Goal 1] ),
    FILTER (
        ALLSELECTED ( 'Middle Employee Goals'[Date] ),
        'Middle Employee Goals'[Date] <= MAX ( 'Dates'[Date] ) &&
        MONTH ( 'Middle Employee Goals'[Date] ) = MONTH ( MAX ( 'Dates'[Date] ) ) &&
        YEAR ( 'Middle Employee Goals'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) )
    )
)
    Entry MTD Goal = 
CALCULATE (
    SUM ( 'Entry Employee Goals'[Goal 1] ),
    FILTER (
        ALLSELECTED ( 'Entry Employee Goals'[Date] ),
        'Entry Employee Goals'[Date] <= MAX ( 'Dates'[Date] ) &&
        MONTH ( 'Entry Employee Goals'[Date] ) = MONTH ( MAX ( 'Dates'[Date] ) ) &&
        YEAR ( 'Entry Employee Goals'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) )
    )
)

    And the final output is as below:

     

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

KW123 Combine the Senior, Mid, and Entry Employee goals into a single table with an additional column to indicate the employee type (Senior, Mid, Entry). This will act as a bridge table.

 

Create relationships between the bridge table and the Employee list table on the employee ID or title.
Create relationships between the bridge table and the Dates table on the date field.

 

Use the bridge table in your visualizations instead of the individual goal tables. This will ensure that the slicer works correctly across all employee types.

 

Create DAX measures to calculate the MTD goals using the bridge table. 

MTD Goal =
CALCULATE (
SUM ( 'BridgeTable'[Goal]),
DATESMTD ( 'Dates'[Date] )
)

 

Ensure that the slicer is set to filter by store number and is connected to the Employee list table. This will filter the bridge table through the relationships.

  • KW123's avatar
    KW123
    Helper V
    1 year ago

    Hi bhanu_gautam  

    Thank you so much for replying to this!! I created a bridge table with the employee title as a column.  I created a relationship to the employee table through the employee title, as well as to the dates table on date. 

    Now I am getting an error saying that there is ambiguity with my sales table which is housing day to day sales 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi KW123 ,

      You did not provide your data model, so I tried to create several tables myself. However, because you involve too many tables, I am not sure whether the sample data I created is consistent with your actual situation. If it is not consistent or my solution is not helpful to you, please provide sample data in all your tables, thank you!
      Here are my sample datas:

      Here are the relationships:

      Then use these DAXs to create measures:

      Senior MTD Goal = 
CALCULATE (
    SUM ( 'Senior Employee Goals'[Goal 1] ),
    FILTER (
        ALLSELECTED ( 'Senior Employee Goals'[Date] ),
        'Senior Employee Goals'[Date] <= MAX ( 'Dates'[Date] ) &&
        MONTH ( 'Senior Employee Goals'[Date] ) = MONTH ( MAX ( 'Dates'[Date] ) ) &&
        YEAR ( 'Senior Employee Goals'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) )
    )
)
      Mid MTD Goal = 
CALCULATE (
    SUM ( 'Middle Employee Goals'[Goal 1] ),
    FILTER (
        ALLSELECTED ( 'Middle Employee Goals'[Date] ),
        'Middle Employee Goals'[Date] <= MAX ( 'Dates'[Date] ) &&
        MONTH ( 'Middle Employee Goals'[Date] ) = MONTH ( MAX ( 'Dates'[Date] ) ) &&
        YEAR ( 'Middle Employee Goals'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) )
    )
)
      Entry MTD Goal = 
CALCULATE (
    SUM ( 'Entry Employee Goals'[Goal 1] ),
    FILTER (
        ALLSELECTED ( 'Entry Employee Goals'[Date] ),
        'Entry Employee Goals'[Date] <= MAX ( 'Dates'[Date] ) &&
        MONTH ( 'Entry Employee Goals'[Date] ) = MONTH ( MAX ( 'Dates'[Date] ) ) &&
        YEAR ( 'Entry Employee Goals'[Date] ) = YEAR ( MAX ( 'Dates'[Date] ) )
    )
)

      And the final output is as below:

       

      Best Regards,
      Dino Tao
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.