Dax Formula Translation

[Anonymous]

Hello All,

I am currently creating an Overlap Dashboard within PowerBi.  I'm having trouble understanding what a certain DAX function is calculating.  If somone could help translate this Function into Pseudo Code, that would be very helpful, Thank you all!

DateRangeCollisions = CALCULATE(

        COUNTROWS( 'MST - Rev3' ),

        FILTER( 'MST - Rev3',

            'MST - Rev3'[Store Number] = EARLIER('MST - Rev3'[Store Number]) &&

            EARLIER('MST - Rev3'[Remodel Start (E)]) <= DATEADD('MST - Rev3'[Remodel Start (E)],'Overlap in Days'[Overlap in Days Value],DAY) && EARLIER('MST - Rev3'[Remodel Start (E)]) >= DATEADD('MST - Rev3'[Remodel Start (E)],-'Overlap in Days'[Overlap in Days Value],DAY)))

Overlap in Days Value = SELECTEDVALUE('Overlap in Days'[Overlap in Days], 7)

Has Overlap = IF('MST - Rev3'[DateRangeCollisions]> 1, TRUE(), FALSE())

[Wilson_ · Accepted solution]

Hi wkay,

DateRangeCollisions =
VAR StoreNumber = SELECTEDVALUE ( 'MST - Rev3'[Store Number] )
VAR RemodelStart = SELECTEDVALUE ( 'MST - Rev3'[Remodel Start (E)] )
VAR RemodelStartLessOverlap = RemodelStart - 'Overlap in Days'[Overlap in Days Value]
VAR RemodelStartPlusOverlap = RemodelStart + 'Overlap in Days'[Overlap in Days Value]

RETURN
CALCULATE (
    COUNTROWS ( 'MST - Rev3' ),
    FILTER (
        'MST - Rev3',
        'MST - Rev3'[Store Number] = StoreNumber
        && 'MST - Rev3'[Remodel Start (E)] >= RemodelStartLessOverlap
        && 'MST - Rev3'[Remodel Start (E)] <= RemodelStartPlusOverlap
    )
)

I've formatted your DateRangeCollisions measure and sprinkled in some usage of variables to make it easier to read. I trust you to take it from here.

P.S.: I'm guessing somebody else wrote this DAX and is no longer available to help you understand what's going on here. Please, if nothing else, let this be a lesson to document and make things more legible for future you and anybody else who has to look at your work. 🙂

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[Anonymous]

Thank you Wilson (a.k.a. (Resolver II)).  This is the exact situation, I appreciate your help with this!

[Wilson_]

Happy to help. I forgot to mention daxformatter.com is a great resource for re-formatting your DAX. You eventually learn to format it that way - or at least in your own reader friendly style - pretty quickly. (Or at least I did! :D)