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

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 }
    )
magnify-bi-com's avatar
magnify-bi-com
Helper I
4 years ago

Hey Alexis,

 

I thought this worked but it's actually filtering out the most recent transaction if the status isn't 1 or 2.

 

What I need to is find the DISTINCTCOUNT of customers, returning their most recent transaction and the related status to that transaction.

 

I can actually filter out the status within the visual itself, so I don't need that part, but I want to be able to create a dynamic table, that looks back over the last 90 days from any date, and then creates a filtered table that only shows the most recent transaction for that customer and the corresponding status.

 

Thanks

  • 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.