Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have users who submit a form each day.
The form lets users select a number of options ( in this example, food items).
The fact table has a row for each food item per user per submission.
The issue occurrs when a user submits multiple times per day, giving incorrect counts.
For all my measures i need to only consider the records from the last submission per day.
Is this possible?
This is a link to a pbix with sample data
User submission Value 2 Submitted DateTime
a submission 1 cheese 01/01/2020 01:00
a submission 1 egg 01/01/2020 01:00
b submission1 cheese 01/01/2020 01:03
b submission 2 mayo 01/01/2020 01:06
c submission 1 sausage 01/01/2020 12:10
c submission 1 icecream 01/01/2020 12:10
c submission 1 jello 01/01/2020 12:10
a submission 1 cream 02/01/2020 05:00
b submission 1 egg 02/01/2020 06:50
c submission 1 bread 02/01/2020 07:04
a submission 1 cheese 03/01/2020 08:00
b submission 1 egg 03/01/2020 08:00
c submission 1 mayo 03/01/2020 08:02
c submission 1 icecream 03/01/2020 08:03
d submission 2 egg 03/01/2020 08:05
d submission 2 bead 03/01/2020 08:05
d submission 3 bread 03/01/2020 08:06
d submission 3 egg 03/01/2020 08:06
d submission 3 mayo 03/01/2020 08:06
Thank you for any help you can provide.
Solved! Go to Solution.
let me know if you'd like to get below results:
Pbix attached.
let me know if you'd like to get below results:
Pbix attached.
maybe you can also try create a column and a measure
Column = DATEVALUE('Sheet1'[Submitted DateTime])
Measure =
MAXX(FILTER(Sheet1,'Sheet1'[Submitted DateTime]=CALCULATE(MAX('Sheet1'[Submitted DateTime]),ALLEXCEPT(Sheet1,Sheet1[User],Sheet1[Column]))),Sheet1[Value 2])
However, it's better to have second in your data. As you can see, c submitted three times on the same time on 2020/1/1.
I tried and it showed the first value.
Proud to be a Super User!
thanks @ryan_mayu
I couldnt quite get that to work with the dataset unfortunately.
I came up with a bit of a hack, adding a column that shows the submission datetime ranking desc.
This means the most recent submission per day per user will always be ranked 1.
I can then just use a filter to filter where this calculated column is 1. Its not as elegant as all in dax, but its easier to understand.
rankx(filter(Sheet1, Sheet1[User] = EARLIER(Sheet1[User])),Sheet1[Submitted DateTime],,DESC)
One thing i dont understand is why this query below does return the same answer.
var usr = SELECTEDVALUE(Sheet1[User])
return
rankx(filter(Sheet1, Sheet1[User] = usr),Sheet1[Submitted DateTime],,DESC)
advice on why these two are not similar would be very helpful to improve my understanding.
@wilson_smyth , what is expected output.
you can use LastNONBLANKVALUE(table2[Submitted DateTime], MAx(Table[Value2]))
You need to have a date in your visual and other groups to force context. All other calculations need to be like that
@wilson_smyth - You should be able to use Lookup Min/Max to handle this:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |