Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wilson_smyth
Post Patron
Post Patron

only using the last submission per day

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.

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

Hi @wilson_smyth 

 

let me know if you'd like to get below results:

004.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

5 REPLIES 5
v-diye-msft
Community Support
Community Support

Hi @wilson_smyth 

 

let me know if you'd like to get below results:

004.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
ryan_mayu
Super User
Super User

@wilson_smyth 

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])

1.PNG

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.





Did I answer your question? Mark my post as a solution!

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.

 

Its using a variable, which could be substituded for use of the EARLIER function.

 

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.

 

amitchandak
Super User
Super User

@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

Greg_Deckler
Super User
Super User

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.