Forum Discussion

Vitaliy's avatar
Vitaliy
Icon for Helper I rankHelper I
7 years ago

Build calculates between tables without relationships

Hi to all!

 

For several days I have been fighting a task and can’t understand how to solve it.
In the test.pbix my test report.
I have such a model with links:

 

I need to build a table and several single indicators, for example like this:

And I have a question:
How can I build a model or how can I change the "LogHours" measure  so that the lines in the table above do not duplicate LogHours measure (for the selected period, the employee has only 1 LogHour, and not one in each of the months)?

So the table should look like this:

Duplication occurs due to the fact that the worklogs and DateTime_Vacations tables are not connected, and with the current model, if they are linked, the relationship will be inactive, naturally

Thanks!

Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Vitaliy ,

    Please try this formula：

    Measure =
VAR a =
    CALCULATE (
        SUM ( 'worklogs'[logtime] ),
        FILTER (
            'DateTime_Vacations',
            YEAR ( 'DateTime_Vacations'[Date] ) = YEAR ( MAX ( 'worklogs'[date_only] ) )
                && MONTH ( 'DateTime_Vacations'[Date] ) = MONTH ( MAX ( 'worklogs'[date_only] ) )
        )
    )
RETURN
    a / 3600

    Here is the output.

    Best Regards,

    Cherry

    • Vitaliy's avatar
      Vitaliy
      Icon for Helper I rankHelper I
      7 years ago

      Thanks for your answer, v-piga-msft !

      I want to ask additional question: if i choose all users in username slicer, then my total value for measure is 9,83 (so all worklogs were included) and this is not what i'm expect (
      What measure i must use for get correct total value (for example, if i want to show this 1 hour in KPI card) ?

       

      I apologize for providing incomplete conditions and thanks for help