1 Date axis, 2 measures, each measure based on a different dimension

[PetyrBaelish]

I have a fact table with the following columns that's storing data on helpdesk incidents

ReportedDateKey (text, e.g. 20190112)

ClosedDateKey (text, e.g. 20190209)

Completed (integer, a 1 if the incident is complete, 0 if it isn't)

I have a measure called Incidents Completed which is a sum of the Completed column.

The ReportedDateKey and ClosedDateKey columns are related to 2 dimension tables - both are dates tables that store information about that date, e.g. the year, month, week number, month-year (e.g. 2018-06) etc.

I am trying to create a line and stacked column visual in Power BI that shows:

- An axis with year and month-year, allowing for drilldown. I'm currently using the year and month-year columns from my ReportedDate dimension

- 2 lines, one for calls reported and 1 for calls completed.

Whatever I seem to do to try and get two separate lines doesn't seem to work. My two lines are identical (in my data every incident is completed - but my point is there should be a variation due to the timing of the incident being reported and the incident being completed, so incidents logged might be 30 in January, 40 in February, but incidents closed might be 25 in January, 45 in February - I want my visualisation to show, by 2 lines, the delay in incidents being reported and completed). 

I understand why, as my axis is based on the reported date dimension table, but I don't know how to involve the second dimension on the same visual.

I'm using Visual Studio to configure my tabular model (live connection) - ideally I'd like to have two measures called Incidents Reported and Incidents Completed and that these measures would have an awareness of the times that they belong to when adding them to a measure with a date based X axis.

[v-frfei-msft · Accepted solution]

Hi @PetyrBaelish ,

One sample for your reference. Please check the steps as below.

1. Create two calculated column in the fact table.

ClosedDateKey1 = DATE(LEFT('Table1'[ClosedDateKey],4),MID('Table1'[ClosedDateKey],5,2),RIGHT('Table1'[ClosedDateKey],2))

ReportedDateKey1 = DATE(LEFT('Table1'[ReportedDateKey],4),MID('Table1'[ReportedDateKey],5,2),RIGHT('Table1'[ReportedDateKey],2))

2. Create a date table and make the relationship between it and the fact table as below. Please note here the relationship is inactive.

date = CALENDAR(DATE(2019,01,01),DATE(2019,12,31))

3. Then we can create two measures to get the excepeted result we need.

Closed = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Completed]=1),USERELATIONSHIP(Table1[ClosedDateKey1],'date'[Date]))

repo = CALCULATE(COUNT(Table1[Completed]),FILTER(Table1,Table1[Completed]=1),USERELATIONSHIP(Table1[ReportedDateKey1],'date'[Date]))

Pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @PetyrBaelish ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank