Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct count on dynamic MIN(datekey)

New with DAX so bear with me.   I am trying to derive a count of customers with a first visit, filtered by brand, or channel, or date, or a combination of all.   Here is an example data set:   ...
  • Anonymous's avatar
    Anonymous
    6 years ago

     

    // Let's say that the table you're showing
    // is the fact table, FT, and it's connected
    // on the obivous fields to dimensions:
    // Customer, Brand, Channel, Date.
    // The other fileds, Metric and Venue,
    // should also have their own dimensions
    // but I'll use Metric as it is. Please
    // note as well, that you should never
    // drop a column from a fact table onto
    // the canvas if there is a dimension
    // attached to it. This will almost always
    // mess up calculations. Such columns should
    // always be hidden.
    
    // Please come up with a better name
    // for the measure. The name of the
    // measure should give a hint at what
    // it really does. Thanks.
    
    [# Cust With First Visit] =
    var __startDate = MIN( 'Date'[Date] )
    var __endDate = MAX( 'Date'[Date] )
    var __result =
        SUMX(
            Customer,
            var __firstDate =
                CALCULATE(
                    MIN( FT[Metric Date] ),
                    // If you don't want to intersect
                    // the filter on Metric with "visit",
                    // just remove the keepfilters wrapping
                    // and only leave the condition under it.
                    KEEPFILTERS( FT[Metric] = "visit" ),
                    ALL( 'Date' )
                )
            return
                (__firstDate >= __startDate)
                *
                (__firstDate <= __endDate)
        )
    return
        if( __result, __result )