Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Using related in a measure

Hi,   I have a simple query where i count the no. issues closed during each month and hence i created a date table and have a n:1 (GetIssue[Closed_date] : DateKey[Date])relationship with my date ta...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this measure

     

    =CALCULATE(DISTINCTCOUNT(GetIssues[id]),USERELATIONSHIP(GetIssues[Closed_date],DateKey[Date]))

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thank you! I had initially tried this out but when i combine it with additional filters it wouldnt work. Am not sure if my query with filters and calculate is wrong For example : 

 

ClosedBugs = CALCULATE(DISTINCTCOUNT(GetIssues[id]),FILTER(GetIssues,'GetIssues'[issuetype] ="Bug" && 'GetIssues'[Priority] in {P1,P2} ,USERELATIONSHIP(GetIssues[Closed_date],DateKey[Date])) 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Try this

 

=CALCULATE(DISTINCTCOUNT(GetIssues[id]),'GetIssues'[issuetype]="Bug",'GetIssues'[Priority]="P1"||'GetIssues'[Priority]="P1", ,USERELATIONSHIP(GetIssues[Closed_date],DateKey[Date]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I settled for adding filters under the visual filters, and just using distinct count and userelationship.

     

    Got stuck with a new problem, am trying to calculate backlog bugs i.e all bugs created before the next month but closed after next month.

    My query is the following but i think am going wrong with the order of precendence/filter because my values are way off. 

     

    backLogBugs = CALCULATE(DISTINCTCOUNT(GetIssues[id]),Filter(GetIssues, (GetIssues[created]< Related(DateKey(StartofNextMonth) && GetIssues[customfield_10030] > Related(DateKey(StartofNextMonth)) || (GetIssues[created] < Related(DateKey(StartofNextMonth) && GetIssues[status.name] <> "Closed"))

     

    Thanks for you help!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      I am not clear.  Share some data and show the expected result.