Forum Discussion

govi's avatar
govi
Helper II
5 years ago
Solved

Measure to find first & second instance

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 t...
Need Help
  • HotChilli's avatar
    HotChilli
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.