Forum Discussion

h4n1234's avatar
h4n1234
Frequent Visitor
4 years ago
Solved

DAX - How to: Identify cycles & calculated score based on pair logic

Hi - I am looking for some advice / guidance on how to solve the following DAX challenge   Goal: To group the meetings by cycle to find the difference between the score of their first meeting of th...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi h4n1234 ,

     

    I have created many measures to achieve such output, please follow:

    Rank by Type = RANKX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID])),CALCULATE(MAX('Table'[Meeting Type])),,ASC,Dense)
    Start Flag = 
var _pre= MAXX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID]) && [AttendeeIndex]=MAX('Table'[AttendeeIndex])-1),[Rank by Type])
return 
SWITCH(TRUE(),  MAX('Table'[AttendeeIndex])=1,1, _pre>[Rank by Type],1)
    each pair start rank = 
var _rank=RANKX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID]) && [Start Flag]=1),CALCULATE( MAX('Table'[AttendeeIndex])),,ASC,Dense)
return IF([Start Flag]<>BLANK(),_rank,BLANK())
    End Flag = 
var _next= MAXX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID]) && [AttendeeIndex]=MAX('Table'[AttendeeIndex])+1),[Rank by Type])
return 
SWITCH(TRUE(), _next<>BLANK()&& _next<[Rank by Type],1 , _next=BLANK(),1)
    each pair end rank = 
var _rank=RANKX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID]) && [End Flag]=1),CALCULATE( MAX('Table'[AttendeeIndex])),,ASC,Dense)
return IF([End Flag]<>BLANK(),_rank,BLANK())

    Now we could calcualte the Cycle and Include:

    Cycle = IF([each pair start rank]<>BLANK(),[each pair start rank],IF([each pair end rank]<>BLANK(),[each pair end rank]))
    Include = IF( OR([each pair start rank],[each pair end rank])=FALSE() || [each pair start rank]=[each pair end rank] ,BLANK(),1)

     

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi h4n1234 ,

 

I have created many measures to achieve such output, please follow:

Rank by Type = RANKX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID])),CALCULATE(MAX('Table'[Meeting Type])),,ASC,Dense)
Start Flag = 
var _pre= MAXX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID]) && [AttendeeIndex]=MAX('Table'[AttendeeIndex])-1),[Rank by Type])
return 
SWITCH(TRUE(),  MAX('Table'[AttendeeIndex])=1,1, _pre>[Rank by Type],1)
each pair start rank = 
var _rank=RANKX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID]) && [Start Flag]=1),CALCULATE( MAX('Table'[AttendeeIndex])),,ASC,Dense)
return IF([Start Flag]<>BLANK(),_rank,BLANK())
End Flag = 
var _next= MAXX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID]) && [AttendeeIndex]=MAX('Table'[AttendeeIndex])+1),[Rank by Type])
return 
SWITCH(TRUE(), _next<>BLANK()&& _next<[Rank by Type],1 , _next=BLANK(),1)
each pair end rank = 
var _rank=RANKX(FILTER(ALL('Table'),[AttendeeID]=MAX('Table'[AttendeeID]) && [End Flag]=1),CALCULATE( MAX('Table'[AttendeeIndex])),,ASC,Dense)
return IF([End Flag]<>BLANK(),_rank,BLANK())

Now we could calcualte the Cycle and Include:

Cycle = IF([each pair start rank]<>BLANK(),[each pair start rank],IF([each pair end rank]<>BLANK(),[each pair end rank]))
Include = IF( OR([each pair start rank],[each pair end rank])=FALSE() || [each pair start rank]=[each pair end rank] ,BLANK(),1)

 

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.

h4n1234's avatar
h4n1234
Frequent Visitor
4 years ago

So now I want to find the difference between the first score of a cycle and the last (where include = 1). And then plot the values on a graph. 

 

Any ideas how I'd go about this?

 

I've tried switch statements to get the first and last scores but this will not format in a graph (it is also very slow) - thanks in advance!

8. First Score = 
VAR _score =
    SWITCH (
        TRUE (),
        [2. Start Flag] = 1
            && [7. Include] = 1, SUM ( 'Table'[Score] ),
        BLANK ()
    )
RETURN
    _score