Forum Discussion

magnify-bi-com's avatar
magnify-bi-com
Helper I
4 years ago
Solved

DAX Formula for Cross Table Filtering / Date Logic

I am trying to figure out how to write a DAX solution that requires 2 tables being filtering by Date and Status, respectively.   I have 2 tables that are too big to Merge within the Query Editor, a...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Yeah. You might want to define it first as a variable though.

     

    Count90 =
VAR DynamicDate = [DynamicDateMeasure]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Pass'[Customer] ),
        Sale[Created] > DynamicDate - 90,
        'Pass'[Status] IN { 1, 2 }
    )
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    How about this then?

    CountLast90 =
VAR DynamicDate = TODAY () - 90
VAR StatusVals = { 1, 2 }
VAR AddCreatedCol =
    ADDCOLUMNS (
        'Pass',
        "Created", LOOKUPVALUE ( Sale[Created], Sale[ObjID], 'Pass'[SaleID] )
    )
VAR PassFiltered = FILTER ( AddCreatedCol, [Created] > DynamicDate )
VAR AddLastCreated =
    ADDCOLUMNS (
        PassFiltered,
        "LastCreated",
            MAXX (
                FILTER ( PassFiltered, [Customer] = EARLIER ( [Customer] ) ),
                [Created]
            )
    )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER ( AddLastCreated, [Created] = [LastCreated] && [Status] IN StatusVals ),
            [Customer]
        )
    )

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

How about this then?

CountLast90 =
VAR DynamicDate = TODAY () - 90
VAR StatusVals = { 1, 2 }
VAR AddCreatedCol =
    ADDCOLUMNS (
        'Pass',
        "Created", LOOKUPVALUE ( Sale[Created], Sale[ObjID], 'Pass'[SaleID] )
    )
VAR PassFiltered = FILTER ( AddCreatedCol, [Created] > DynamicDate )
VAR AddLastCreated =
    ADDCOLUMNS (
        PassFiltered,
        "LastCreated",
            MAXX (
                FILTER ( PassFiltered, [Customer] = EARLIER ( [Customer] ) ),
                [Created]
            )
    )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER ( AddLastCreated, [Created] = [LastCreated] && [Status] IN StatusVals ),
            [Customer]
        )
    )

 

magnify-bi-com's avatar
magnify-bi-com
Helper I
4 years ago

I'm pretty sure you are a genius, that is getting me the overall figure straight off the bat and in a lightening time. Wow, that is impressive! Thank you very much.

 

If I wanted to put this Measure in a Line chart, with the Date in the Axis, would it be possible that it took the date from the axis and went back 90 days from that date to bring back the count of customers that meet that criteria?

 

I'm actually blown away by that solution!

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Replace TODAY() with something that reads in the local date context like MAX ( dimDate[Date] ) using whatever your x-axis column is.