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.
Dear Community,
Hope my post finds you well and healthy.
I have the following problem. I want to show how many tickets were opened on a particular date and how many were closed if any.
NOTE: I have created calendar table.
1. I have 1 core measure which is # Of Cases = =DISTINCTCOUNT(OpenedAndClosedTicketsF[Case Number])
2. Then I created Open Cases =CALCULATE([# Of Cases],USERELATIONSHIP(CalendarTable[Dates],OpenedAndClosedTicketsF[Date/Time Opened.1]))
3. And Closed Cases =CALCULATE([# Of Cases],USERELATIONSHIP(CalendarTable[Dates],OpenedAndClosedTicketsF[Date/Time Closed.1]))
4. Then I created the measure Open Minus Closed =[Open Cases] - [Closed Cases]
5. And lastly I created the backlog measure as it follows: =CALCULATE([Open Minus Closed ],FILTER(ALL(CalendarTable),CalendarTable[Dates]<=MAX(CalendarTable[Dates])))
Everything works perfectly fine If I do not filter the table by any period or year.
As the backlog measure considers all the previous dates...
If I am about to filter the table only by Aug-2021, these are the results I am getting:
Obviously when I filter the measure is not considering the previous backlog and this is my problem.
Would you please help me correct it?
All due respect,
Radoslava
It really is important which table you take fields from and put in your slicers and visuals. I'll give you a hint: Fact tables should always have all their columns hidden (never put on the canvas) and slicing and dicing should always be performed through dimensions. Stick to this rule and you'll be less surprised.
By the way, if you have to put columns from a fact table into a visual.... well, that means your design is incorrect.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |