topic Re: New Column that returns a single value based on date? in DAX Commands and Tips

New Column that returns a single value based on date?

sharpedogs — Wed, 27 Jan 2021 03:28:41 GMT

Hi,

I have the below data set with dates as to when a customer filled out a survey. I want to create two new columns that shows me their response based on The First Survey they filled out (earliest date). Then a second new column that does the same but shows the response for the last survey they filled out.

I've tried a bunch of claculations using values and first/last date. But i can't seem to get it correct.

Customer	Date	Response	OUT PUT (First Response)
Chad	Jan 1st 2020	Pro	Pro
Sam	Jan 5th 2020	Detract	Detract
Chad	Jan 9th 2020	Pas	Pro
Chad	Jan 11th 2020	Pas	Pro
Sam	Jan 2th 2020	Pro	Pro
Dave	Jan 4th 2020	Pas	Pas
Dave	Jan 1st 2020	Pas	Pas

Re: New Column that returns a single value based on date?

lbendlin — Wed, 27 Jan 2021 04:00:41 GMT

Power Query or DAX? Calculated Column or Measure?

Re: New Column that returns a single value based on date?

sharpedogs — Wed, 27 Jan 2021 04:13:09 GMT

I need to show the values in a column. So either PowerQ or, calculated column or a new formular column would work.

Re: New Column that returns a single value based on date?

lbendlin — Wed, 27 Jan 2021 04:53:34 GMT

Here is a very crude variation. I know this can be improved with LASTNONBLANKVALUE but i need to re-learn that.

Also, "Jan 2th" - really?!

Re: New Column that returns a single value based on date?

lbendlin — Wed, 27 Jan 2021 05:09:59 GMT

Here's a slightly more noble version

First Response =

var c = [Customer]

var f = minx(filter(Surveys,[Customer]=c),[DateValue])

return minx(filter(Surveys,[DateValue]=f && [Customer]=c),[Response])

What do you expect to happen when the user submits multiple surveys on the same day?

Re: New Column that returns a single value based on date?

sharpedogs — Wed, 27 Jan 2021 13:36:07 GMT

i was doing it at 11pm.. after spening 2 hours messing around with it.. give me a break...lol.. I'll try your solution below..

thanks

Re: New Column that returns a single value based on date?

sharpedogs — Wed, 27 Jan 2021 13:44:00 GMT

A customer (person) is only sent one survey a quarter. So there is no issue with multiple on the same day.