A Cool DAX Challenge

DAX DAX Calculations
1

All,

An excellent DAX Challenge by my friend, Chandeep Chhabra.

Is this what I should have been doing at 3 AM? Perhaps not – but when a clever DAX challenge rings the bell, I answer the door. :smile:

I went to post my solution on his blog, and saw that it already been solved by another Enterprise DNAer. I’ll give you one guess who… :laughing:

My solution:

Summary

I’m not a big fan of calculated columns, but this one works more cleanly as a calculated column than a measure, so I did that here. In the real world I would just use Power Query.

No. of Stints = 

VAR CurrRole = Data[Role]
VAR CurrDate = Data[Date]
VAR CurrEmp = Data[EmpCode]
VAR PrevDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER (
            ALL ( Data ),
            Data[EmpCode] = CurrEmp
                && Data[Date] < CurrDate
        )
    )
VAR PrevRole =
    CALCULATE (
        MAX ( Data[Role] ),
        FILTER (
            ALL ( Data ),
            Data[Date] = PrevDate
                && Data[EmpCode] = CurrEmp
        )
    )
VAR Stints =
    SWITCH (
        TRUE (),
        PrevDate
            = BLANK (), 1,
        Data[Role] <> PrevRole, 1,
        0
    )
RETURN
    Stints

Enjoy!

  • Brian

P.S. he specifies clearly that you are to use DAX, probably because Power Query would make quick work of this using the “Feldmann Shift”.

2

I’m going for @AntrikshSharma as the problem solver who beat you to it!

1 Like
3

@DavieJoe ,

A very strong guess, but no.

FYI – I just looked in the YouTube comments and a very clever commenter named Ytzen Terpstra figured out how to do the Feldmann Shift in DAX (he did need to add an index column in PQ first) and essentially solved this problem in one line of DAX. :grinning:

  • Brian
1 Like
4

Hi @BrianJ

Today only I saw this post and tried. I think using calculated column we can use simple functions instead of measure.

image
image1192×423 33.6 KB