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 table and this is an inactive relationship so i use USERRELATIONSHIP. But my final values seem to be very less. Any insight will be helpful 

 

PowerBI.PNGDate table

Query :

ClosedBugs = CALCULATE(DISTINCTCOUNT(GetIssues[id]),FILTER(GetIssues,'GetIssues'[Closed_date] >=RELATED(DateKey[StartofCurrentMonth]) && 'GetIssues'[Closed_date]< RELATED(DateKey[StartofNextMonth]),USERELATIONSHIP(GetIssues[Closed_date],DateKey[Date])) 

 

 

Thanks,

Aishwarya

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

     

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    I'd like to suggest you take a look at following blog to know how to use userelationship function in calculate column:

    USERELATIONSHIP in Calculated Columns

     

    If above not help, can you please share some sample data to test?

     

    Regards,

    Xiaoxin Sheng

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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]))