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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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/339586.
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.