Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 issue at hand). Currently, the Fact table is connected to a date table using the Date Requested Field.
I created 2 measures:
# of Tickets Created = Countrows(FactTBL)
# of Completed Tickets = Calculate(Countrows(FactTBL),Status="Complete")
I need to plot the # of tickets created and the # of tickets completed by Month. But, i need the number completed to be based on when they were completed and the number created based on when they were created. Currently, the chart is showing the values based on the Date Requested field.
ex.
ID | Date Requested | Date Created | Date Finished | Status |
1 | 1/1/2016 | 1/5/2016 | 2/1/2016 | Complete |
2 | 2/1/2016 | 2/1/2016 | 3/15/2016 | Complete |
3 | 2/1/2016 | 2/8/2016 | Open | |
4 | 3/1/2016 | 3/5/2016 | 4/10/2016 | Complete |
based on the example above should show for Jan 1 created an 0 completed, for Feb. 2 created and 1 completed, for Mar 1 created and 1 complete. and for April 0 created and 1 complete. That's not what i am getting. Any suggestions on how to resolve this?
Solved! Go to Solution.
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
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
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
71 | |
70 | |
43 | |
31 | |
26 |
User | Count |
---|---|
89 | |
49 | |
44 | |
38 | |
37 |