Forum Discussion

Katerina_Bis's avatar
Katerina_Bis
Frequent Visitor
9 years ago
Solved

ytd

Hello,   In power BI desktop my problems is that I need to combine 2 different YTD measures (calculated on 2 tables, thus 2 different date tables used too). The program does not allow me to includ...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi Katerina_Bis,

     

    When you added measures *YTD_turnover amd *YTD_timelog to a table visual, which error threw out? Please try to set the cross filtering direction to Both for each relationship among those tables. For more information, see: Create and manage relationships in Power BI Desktop.

     

    Best Regards,
    QiuyunYu

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
9 years ago

Hi Katerina_Bis,

 

I got your point but could you please share your sample data or data structure? so i could quickly figure out solution.

Katerina_Bis's avatar
Katerina_Bis
Frequent Visitor
9 years ago

Hello,

 

I cannot share the data sample. But I have drafted the data structure and the desirable result.

Hope it helps.

data_structure

  • tringuyenminh92's avatar
    tringuyenminh92
    Memorable Member
    9 years ago

    Hi Katerina_Bis,

     

    I think you should have one more Dates table for both of Finance_data and JIRA_Data as side-by-side topic and you could use date column of that Dates table for slicer.(Finance and JIRA will refer date column of new DATES table)

    One more concern that *YTD_turnover and *YTD_timelog are default columns from sources or calculated measures? if they are calculated measure that computed by DATE_FIN and DATE_LOG, you need to adjust formula to compute by new Dates table.

    • Katerina_Bis's avatar
      Katerina_Bis
      Frequent Visitor
      9 years ago

      Hello @tringuyenminh92,

      I have added extra date table DATE_COMMON and created "Many to One (*:1) relationship between date in DATE_COMMON and Finance_data and another  "Many to One (*:1) relationship between date in DATE_COMMON and Jira_data.

      One of the relationship is NOT active - dashed line between fields.

      Here is the error message I get when trying to tick the box "Make this relantionship active":

      (note : StExportAll = Finance_data)

      YTD values are calculated measures, calculated accordingly on 2 date tables, for each type of data: Finance and Jira.

      • tringuyenminh92's avatar
        tringuyenminh92
        Memorable Member
        9 years ago

        Hi Katerina_Bis,

         

        It's correct. you need to choose one active and another inactive in this case. And for some aggregation in DAX, you could use userelationship() method to access inactive relationship for computing.

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi Katerina_Bis,

     

    When you added measures *YTD_turnover amd *YTD_timelog to a table visual, which error threw out? Please try to set the cross filtering direction to Both for each relationship among those tables. For more information, see: Create and manage relationships in Power BI Desktop.

     

    Best Regards,
    QiuyunYu

    • Katerina_Bis's avatar
      Katerina_Bis
      Frequent Visitor
      9 years ago

      hello v-qiuyu-msft

       

      I have changed the relationship between clients tables from "Both" to "Single" for Cross filter direction and it solved my problem.

      thank you for the usefull link to the theory material.

       

      Kind regards,

      Katerina

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        9 years ago

        Hi Katerina_Bis,

         

        It seems the issue was solved. If that is a case, would you please mark a help reply as an answer so that we can close the thread?

         

        Best Regards,
        Qiuyun Yu