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]
        )
    )

     

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

Thanks for getting back to me Alexis, you're very good, if I wanted this to work for a dynamic date, would I just put my [Date] field where "TODAY()" is?

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

    What would the date measure be?

     

    Essentially, I want to put this Count90 Measure in a table with Dates and want each date to look at the 90 days previous to this date and run this measure. Would it be something like MAX([Date])?

     

    Thanks again for all your help, I really appreciate it.

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      Yeah, it would look something like MAX ( Sale[Date] ), which gives the maximum date within the local filter context. So if you had Sale[Date] for the rows in a table visual, it would grab the date from that row.

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