Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I have a dataset which keeps track of when people report issues and which issues they reported. A 0 code means no problem.
People can report several issues at a time and they get entered as different items then. The amount is a simple 0 if problem code is 0 else 1.
I want to be able to show for a certain date what the "current issues" at the time were.
In below table you can see 3 columns, the first is simply the amount but this just add up every issue ever by everyone.
Most recent measure = CALCULATE(SUM('data'[Amount]),LASTDATE('data'[Date])), i don't quite understand what this does but its not what I want. The "most recent table" is a calculated table:
Most recent problem =
FILTER (
ADDCOLUMNS (
'data',
"IfMax", CALCULATE (
MAX ( 'data'[Date]),
FILTER ( 'data', 'data'[ID] = EARLIER ( 'data'[ID]) )
)
),
[IfMax] = [Date]
)
This however also doesn't work, you can see for the 9th of april its blank and that's because that id has a later issue which overrules the 9th of april issue.
I also want to show the amount of issues for different dates I got the cumulative amount of issues in a graph with:
cumma amount= CALCULATE(sum('Most recent problem'[Amount]),FILTER(ALL('Most recent problem'[Date]),'Most recent problem'[Date] <= MAX('Most recent problem'[Date]))) which looks like:
But this isn't the amount for each date, it's just the total for issues where there's no future issue.
Below is a sample date set which I made to work with
https://www.dropbox.com/s/i9fs7bfos4djd4z/Sample%20power%20bi.pbix?dl=0
If things aren't clear you can message me or respond and then I'll try to clarify or edit.
Thank you in advance,
with kinds regard
Daniel
you may want to play with more visualizations. Oftentimes graphical solutions are sufficient
What decision are you trying to support with your report?
Thanks for the reply,
The goal is to get insight into the prevalence of different issues in the past. You could see the code as users and the different codes as qualitative feedback and then coded into one of several issue codes. The goal is to be able to see for example the 4th of April how many of each issue code was “active”, and this for every date.
With the graph and then issues code as problem you get this:
But this just shows an always increasing amout of issues.
Below graph shows that per ID there is only 1 data point. While there should be 4 showing the amount of issues for every date that the ID has one.
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |