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.

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.