Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to join tables in dax

Hi,

Need to join below tables:

Opened Trend =
var vOpenedTicket = Summarize(
'Parent', 'Parent'[Opened Month],"Parent Open Count", COUNT('Parent'[sys_id])
)
var vClosedTicket = Summarize(
'Parent','Parent'[Closed Month],"Parent Close Count", COUNT('Parent'[sys_id])
)
now need to join 2 tables which will have 3 columns like:
Month name, Open Ticket Count, Closed Ticket Count
 
need advise how to do that?
 
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous solution is attached

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

5 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    maybe it would be better to create two new measures in the original Parent table?

    Parent Open Count = CALCULATE(COUNT('Parent'[sys_id]), ALLEXCEPT('Parent', 'Parent'[Opened Month]) )

Parent Close Count = CALCULATE(COUNT('Parent'[sys_id]), ALLEXCEPT('Parent', 'Parent'[Closed Month]) )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

      I need to show line chart based on Month as dimension and 2 measure values like below:

      but when I created 2 measures and use opened montha as dimension it did not work.

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        Anonymous I think the solution is using date dimension table. As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

        https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

         

        After date dimension is added, set a relationship between open and close month with your date dimension in the table, one relation will be active (assuming relationship with open month is active) and other will be inactive.

         

        add following measures

         

        Open Tickets = COUNTROWS ( Table )
Closed Tickets = CALCULATE ( [Open Tickets], USERELATIONSHIP(  DateTable[Date], Table[Closed Month] ) )

         

        In your visual, use month from date dimension and add above measures on the value and everything should work.

         

        I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

        ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡