Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
So i am currently building a report related to tickets.
I have a request to show several counts related to the ticket data.
First is Tickets Opened, Second is comments on tickets, third is closures.
Currently i have two dates Created-Date and Modified-Date. Created is the timestamp when the ticket was created in the system. the modified date is the points in time when the ticket had any changes comments, reassignment, etc.
When using the modified date as a slicer i get the total of tickets touched by the user, their comments, and their closures within the given date range.
when using the created date as a slicer i get the total for tickets created, comments on those tickets, closures on those tickets.
so i am trying to use one slicer in my query to show the total unique count of tickets created within my date range, while also showing the total comments and closures across all tickets within the date range while using the same slicer and not having two slicers each related to their own visuals.
I thought maybe in DAX there may be a way to use the modified date as the slicer. But somehow calculate how many records CREATE datetime falls between the min/max?
I'm open to other suggestions. I also tried to create a date table and duplicate the query and use the date table as the parent and join each query on one of the date fields CREATED/MODIFIED. However this did not work either.
I'm not sure how to accomplish displaying counts of total tickets created based soley off the datetime field while also including unique modifications off its own datetime field.
Example of the two counts posted below. image one is counts when slicing on the creation date
Here between 2/19 and 2/22 we have 34 tickets open, all were closed and 36 comments. being slicing on created date is showing field changes related to the created ticket list.
But if i swap the slicer to the date field related to the changes. the count increases due to this displaying unqiue tickets touched on this date range (51), unique comments made within the date range regardless of the creatoin date, and unique closures.
So im trying to keep a count of how many tickets were created within this range while also displaying all other changes unfiltered by just ones created within that range. so a mixture of the two scenarios above. Even if i was to keep the 51 unique instances were a ticket had been modified but also displaying the 34 count of unqiue tickets also created within the range.
Trying to provide as much info as possible so sorry if im spinning in circles.
thanks for any advice/solutions
Solved! Go to Solution.
so i believe was able to solve this count issue using DAX USERELATIONSHIP with an inactive relationship. my active relationship is on the TIMESTAMP date field to a calendar table i made of dates.
Then i used this on a card visual
i am in the similar boat ,just want to check if this got resolved?
so i believe was able to solve this count issue using DAX USERELATIONSHIP with an inactive relationship. my active relationship is on the TIMESTAMP date field to a calendar table i made of dates.
Then i used this on a card visual
Hi @Red_prov ,
Given the complexity of your tables and relationships , I cannot understand your meaning correctly. Could you please provide your pbix file without privacy information and desired output with more details.
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
So it appears the measure is doing 1s/0s however they arent counting/suming
Heres another measure i created
However even with the table version appearing to display the correct info and 1s when its in the date range. the sum of these doesnt appear to be working
oddly besides not being able to get a total. and in the original screenshot above i posted showing 10/1 on creation has a 1 recorded compared to 9/30 displaying 0.
It appears the higher end of the slicer 10/31 is not counting?
I'm unable to.
I am just trying to count how many times date X is present in DateY start/end if date Y is the slicer. so if i have a slicer of DATE-Y set for the month of janurary. I want to determine how many times DATE-X column has records that fall within the slicer for DATe-Y
@Anonymous I've attempted the following 2 measures with no success.
Currently to have this image. i have the count of CREATEd tied specifically to the lefthand side slicer CREATEd. while the rest of the visuals on the page are tied to the TIMESTAMP dateTime slicer
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
94 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
100 | |
82 | |
63 | |
53 |