How to join tables in dax

[sandip]

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?

[az38]

Hi @sandip 

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]) )

[sandip]

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]

@sandip 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-ca...

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.⚡

[sandip]

Hi,

I have attached a sample data file for reference:

https://drive.google.com/file/d/13CYVgLKtLIjPNco9bR8pGxnQxtMHAltG/view?usp=sharing

can you make a pbix file for me.

[parry2k · Accepted solution]

@sandip 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.⚡