Measure to find first & second instance

[govi]

Hi,

I have a problem.

I have a table with four columns:

DataTime | userId | Question | Answer

The question "What is your weight?" is asked (and answered) about 7 times per userId.

I have to do a calculation with the first & second instance of the answer of the question above.

How do I do that?

Thank you!

govi

[govi]

@HotChilli @v-eqin-msft @amitchandak @Greg_Deckler 

Thank you all for your help! During the holidays I closed my laptop for 2 weeks.

I use @HotChilli 's solution

Thanks again and happy new year!

govi

[v-eqin-msft]

Hi @govi ,

According to my understanding, you want to filter the first/second record based on each userId ,right?

You could use RANKX() function like this to rank by some userId and DateTime:

rank =
RANKX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[userId] = MAX ( 'Table'[userId] )
            && 'Table'[question] = MAX ( 'Table'[question] )
    ),
    CALCULATE ( MAX ( ( 'Table'[DateTime] ) ) ),
    ,
    ASC
)

Then for example, if you want to sum all the weights ranked first/second, use SUMX() function:

Second =
SUMX (
    FILTER ( 'Table', [rank] = 2 && 'Table'[question] = "What's your weight?" ),
    VALUE ( 'Table'[answer] )
)

Or apply 'Table'[question] = "What's your weight?" to page-level filter to simplify measure like this:

First =
SUMX ( FILTER ( 'Table', [rank] = 1 ), VALUE ( 'Table'[answer] ) )

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[govi]

Hi @VGRG and @amitchandak ,

Thanks for you help!

This is an example of the data (as picture and as csv, I cannot attach a txt or xls file):

DateTime;userId;question;answer;
27-11-20 10:41;161694;Are you male or female?;Male;
27-11-20 10:41;161694;What is your age?;39;
27-11-20 10:41;161694;What's your height?;190;
27-11-20 10:41;161694;What's your weight?;120;first
27-11-20 20:06;161696;Are you male or female?;Male;
27-11-20 20:06;161696;What is your age?;51;
27-11-20 20:06;161696;What's your height?;183;
27-11-20 20:06;161696;What's your weight?;92;
01-12-20 20:11;161696;What's your weight?;92;
02-12-20 19:51;161694;What's your weight?;115;second
04-12-20 19:27;163513;Are you male or female?;Male;
04-12-20 19:27;163513;What is your age?;47;
04-12-20 19:27;163513;What's your height?;170;
04-12-20 19:27;163513;What's your weight?;88;
04-12-20 19:34;163513;What's your weight?;92;
07-12-20 20:12;161696;What's your weight?;94;
08-12-20 19:57;161694;What's your weight?;117;third
11-12-20 20:21;163513;What's your weight?;87;
etc;etc;etc;etc;

I need a measure to isolate the first and an measure to isolate the second weight(answer) per user.

When I have them isolated I can use them for a calculation I need to perform.

Can you help?

Thanks a lot!

govi

[HotChilli · Accepted solution]

The ranking should take into account  user and the question so a column would look like this:

RankCol = 
VAR _user = TableQ[userId]
VAR _question = TableQ[question]
RETURN
    RANKX( FILTER (TableQ,  TableQ[userId] = _user && TableQ[question] = _question ),
     TableQ[DateTime],, ASC, Dense)

swap in your table name.

[amitchandak]

@govi , Create this new column Rank and filter for 1,2

rankx(filter(Table, [userId] = earlier([userId])), [DataTime],,asc,dense)

For Rank Refer these links

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[Greg_Deckler]

@govi See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

In your case you will want to use MINX. If you can provide sample data as text and expected output can be more specific.