Forum Discussion

erhodes's avatar
erhodes
Advocate II
9 years ago
Solved

Graph multiple measure that use different dates???

I have a fact table that has rows for service tickets. The table includes collumns for Date Requested, Date Created, Date Finished, and Status (there are others, but they are not important for the is...
Need Help
  • erhodes's avatar
    erhodes
    9 years ago

    Thank you for your assistance. The formula you provided didn't work for me. However, I was able to find a soltuion. I created an inactive relationships for the date fields in my fact table not currently connected to the date table. I wrote the measures that I needed and then wrote another measure using =USERELATIONSHIP. Worked like a charm

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi erhodes,

 

Based on your description, you want to let the measure work on each row, right?

 

If it is a case, you can refer to below code:

 

Test table:

 

 

# of Completed Tickets =

var temp= Calculate(Countrows(TestTable),FILTER(ALL(TestTable),COUNTX(FILTER(TestTable, [Status]="Complete"&& MONTH( TestTable[Date Created])=MONTH( EARLIER(TestTable[Date Finished]))),TestTable[Date Finished])))

return

if(temp>0,temp,0)

 

# of Tickets Created =

var temp= Calculate(Countrows(TestTable),FILTER(ALL(TestTable),COUNTX(FILTER(TestTable, MONTH( TestTable[Date Created])=MONTH( EARLIER(TestTable[Date Created]))),TestTable[Date Created])))

return

if(temp>0,temp,0)

 

Result:

 

Notice: ‘# of Tickets Created’ measure displays the total tickets created in the same month.

 

Regards,

Xiaoxin Sheng

erhodes's avatar
erhodes
Advocate II
9 years ago

Thank you for your assistance. The formula you provided didn't work for me. However, I was able to find a soltuion. I created an inactive relationships for the date fields in my fact table not currently connected to the date table. I wrote the measures that I needed and then wrote another measure using =USERELATIONSHIP. Worked like a charm