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?

Count90 = 
CALCULATE (
    DISTINCTCOUNT ( 'Pass'[Customer] ),
    Sale[Created] > TODAY () - 90,
    'Pass'[Status] IN { 1, 2 }
)
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]
        )
    )