Use multiple dates in a slicer

[nextwouter]

Hi Guys,

For my internship I'm building a BI tool in microsoft power bi. The 2 main things that need to be visualized are storypoints and hours. I want to show them both in the same visual. Right now I'm using a clustered bar chart with the storypoints and hours on 1 side and the members of a team on the other axis. The problem I run in to is that the storypoints are measured on another date then the time registration, see the datamodel below. The storypoints are measured on the field jira_sprint.dStartdate and the time registration is measured on de date field Jira_Worklog.dStarted. What i want to do is have 1 slicer for both this date fields. The problem is that I can't put an active relationship between them because the tables already have an active relationship through the issue table. I run in to the same problem if I use a seperate DATE table. I've been struggling for quite a few days so I hope you guys can help me out.

Regards!

[v-huizhn-msft]

Hi @nextwouter,

You can create a date table including distinct Jira_Worklog.dStarted and field jira_sprint.dStartdate. Then you can create realtionships between date table and Jira_Worklog, date table and jira_sprint.

1. Create a date table by clicking "New Table" under Modeling on Home page using the formula below.

Date_Table =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( jira_sprint, "Date", jira_sprint[dStartdate] ),
        SELECTCOLUMNS ( Jira_Worklog, "Date", Jira_Worklog[dStarted] )
    )
)

2. Create relationship between date table and Jira_Worklog, date table and jira_sprint.

3. Create a slicer including Date_Table[Date], the slicer will filter both Jira_Worklog.dStarted and field jira_sprint.dStartdate.

Please respond us if you have any other issue.

Best Regards,

Angelia

[nextwouter]

Hi @v-huizhn-msft .

I've added a datetable using your formule and manage to tweak the storypoints and hours formula to let them accept the Date_table as a single slicer. The data now is correct for the amount of stoypoints, but for the amount of time meauserd it still seems te be of. I've made the following formule for the time measurement: 

Uren = CALCULATE((SUM(jira_worklog1[iSeconds])/3600);

                        USERELATIONSHIP(jira_worklog1[dStarted] ; Date_Table[Date]))

Could it have something to do with the commenct of @MFelix about needing the date_table. date field in 1 of the axis? Currently I'm using the assignee field on the left axis and de storypoints en hours on the horizontal one. 

[v-huizhn-msft]

Hi @nextwouter,

Yes, you can use date_table. date and check it you gets the expected result.

Best Regards,
Angelia

[MFelix · Accepted solution]

Hi @nextwouter,

You need to add a measure based on the inactive relationship something like this:

Count =
CALCULATE (
    COUNT ( Table[Column] ),
    USERELATIONSHIP ( Table[Date], DateTable[Date] )
)

Then use this on your chart

Regards,

MFelix

[nextwouter]

Thanx for your reaction. I tried to change the measures that sum up the storypoints and hours. The measures used to be: 

Hours = SUM(jira_worklog1[iSeconds])/3600); 

StoryPoints = SUMX(

                                                         FILTER(jira_issue;jira_issue[cStatus]= "Done"||jira_issue[cStatus]= "Resolved"); [cEstimate]) 

And i changed it in: 

Hours= CALCULATE((SUM(jira_worklog1[iSeconds])/3600); USERELATIONSHIP(jira_worklog1[dStarted] ; 'Date'[Date]))

StoryPoints = SUMX(

                                                         FILTER(jira_issue;jira_issue[cStatus]= "Done"||jira_issue[cStatus]= "Resolved"); [cEstimate]) 

                USERELATIONSHIP(jira_sprint[dStartDate] ; 'Date'[Date])

I get an error on the storypoints formula and the hours formule gives met data that's off. 

[MFelix]

Hi @nextwouter,

When you use this measures you need to place the Date from Date table in your axis do not use any of the other two columns otherwise the numbers will be off.

Regards,

MFelix

[Smauro]

For some reason I've never used USERELATIONSHIP with a date table. Since you have dates in every table, you can try simply always filtering out your measures by <=Max(date) && >=Min(date)

An example is this measure:

CountSthwithDateTable =
 CALCULATE (
     COUNTROWS ( 'jira_issue' ) + 0,
     'jira_issue'[cId],
     FILTER (
         'jira_issue',
         'jira_issue'[dStartDate].[Date] <= MAX ( 'Date'[Day] )
             && 'jira_issue'[dStartDate].[Date] >= MIN ( 'Date'[Day] )
     )
 )

which simply counts how many rows exist in Jira_issue (if my assumption that [cId] is unique is correct), condionally that the [dStartDate] is within range.

Hope this helps 🙂