Non Dev Chart

[Anonymous]

Hi All,

I have a situation here and its confusing for me.

I have two columns in my report i.e Created Date and Closed Date and I have calculated column where i have the distinct count of the Non-dev tickets in my report.

Now I want to create a graph like shown below, how can I proceed with it? I'm confused like how will I show the months in common for the created and closed tickets where as I have different dates for both.

Total number of Non DEv tickets

Created Date

Closed Date

I have these 3 column available in my report.

Expected Chart:

Regards,

Himanshu

[vanessafvg]

Hi Himanshu do you have a date table?

what you need to do is create a date table, link both dates to that date table.

The first date will be the active relationship denoted by an unbroken line and you can create your measure easily by, by default this will be the first date you link

no of tickets by open date = calculate(countrows(tablename))

for the second date you link which will be the closed date the relationship to the date table will have a dotted line (please note the first date you link will be the active so link open date first)

the dotted line means this is an inactive relationship which you will need to activate with your measure.

no of tickets by closed date = calculate(countrows(tablename), userelationship(closeddate, date))

let me know if you have any questions

[vanessafvg]

if you provide some sample date i think it will be much easier to demonstrate  (make sure its in text format)

also can you show what result you arae expecting

[Anonymous]

This is the expected chart:

I'm able to make them seperately but not together.

[vanessafvg]

can you provide some sample data (in text form)

[Anonymous]

This is sample input:

I already have a date table and the relationship created like below:

One is active with the Created Date.

Another is inactive with the closed date.

[Anonymous]

Hi @Anonymous ,

Use below measure and you don't need the inactive relationship and USERELATIONSHIP() function.

Measure = CALCULATE(DISTINCTCOUNT('Table'[WORKITEM]),FILTER('Table',YEAR('Table'[CLOSEDATE])=SELECTEDVALUE('CALENDAR'[Date].[Year])&&MONTH('Table'[CLOSEDATE])=SELECTEDVALUE('CALENDAR'[Date].[MonthNo])))

Best Regards,

Jay

[Anonymous]

@Anonymous Also, can you please confirm what this Measure value is? Is it closed tickets or what?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Yes, the measure counts the closed tickets. Because there an active relationship between date and create date.

Not very clear about your data model, I used date hierarchy Year and Month as axis.

I'll share my sample data with you. please check the attached file.

Best Regards,

Jay

[Anonymous]

Thanks my issue was resolved by using the common time table and the measure.

I created a calculated column for month year number and that resolved my issue.

Regards,

Himanshu

[Anonymous]

Hi Jay,

I'm not sure what is the reason but when I'm dragging fileds from my main table, all the values in the common created date table vanishes. Can' understand the reason as the relationship is also there.

P.S : I've created the measure as suggested. Now stuck on how to use it with my common date table.

[Anonymous]

I have created the below two measures as suggested:

no of tickets by closed date = calculate(countrows('DAX Date Table'), userelationship('devops-query-spr2'[Closed Date],'DAX Date Table'[Date]))

no of tickets by open date = calculate(countrows('DAX Date Table'))

But where does my Non-Dev column goes? Sorry but I'm really confused here.

How to check my Non-Dev ticket values. For closed and created. 

I have a calculated Column for non-Dev tickets which gives me 375 tickets

 I'm still not able to show the closed and created together. Seperately they are working fine.

[amitchandak]

@Anonymous , Based on first look, I think you need a common date table and use userealtionship

Refer this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi @amitchandak I have created the common date table and made the relationship also. one active with created date and one inactive with closed date. But confused how to proceed further.

[amitchandak]

@Anonymous , Create measures.

Measure you create will have a filter on the created date and that is active.

Use userelationship(Table[Closed_date], Date[Date]) In calculates to use close date as the date.

In the date table have month year and plot measure against the month year of date table.

Refer to Hire and Termination in my blog.

[Anonymous]

@amitchandak can you please help in the measure creation.

As per my understanding, i'll be needing 2 measures, one for Created Date and other one for Closed date but i'm not able to get the correct result.

Kindly advice what measures to use.

[Anonymous]

When using the month year from my date table it is not showing the months when the relationship is active. Also, when i make it inactive it displays data month wise but it seems incorrect.

this is when relationship is inactive:

[Anonymous]

I have created the below two measures as suggested:

no of tickets by closed date = calculate(countrows('DAX Date Table'), userelationship('devops-query-spr2'[Closed Date],'DAX Date Table'[Date]))

no of tickets by open date = calculate(countrows('DAX Date Table'))

But where does my Non-Dev column goes? Sorry but I'm really confused here.

How to check my Non-Dev ticket values. For closed and created. 

I have a calculated Column for non-Dev tickets which gives me 375 tickets

 I'm still not able to show the closed and created together. Seperately they are working fine.