Distinct count on dynamic MIN(datekey)

[Anonymous]

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:

I only want to count unique customers where they have had a first visit, dependent on filter context. A first visit is MIN(Metric Date).

I have tried this formula: 

CALCULATE(DISTINCTCOUNT(Customer ID), Metric = "Visit",

FILTER('Table', Metric Date<=MAX('Dim Date'[dim date key])&&Metric Date>=MIN('Dim Date'[dim date key]))

)

The problem with this is that if I filter on all brands and a metric date >=27/08/2019, this measure puts the first visit date as 27/08/2019. However, this is only accurate if there is a filter for brand = 1. If looking at all brands, the first visit date is actually 26/08/2019 for customer 1, and therefore they shouldn't be counted. 

I hope that makes sense, i.e. the first visit date isn't fixed. Across all brands it is the MIN(metric date) regardless of date filter selected. For a single brand or channel selection, it is the MIN(metric date) for that particular brand or channel. 

If anyone can help, I would be very grateful. 

[Anonymous · Accepted solution]

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

[Anonymous]

@Anonymous 

Thank you for the repsonse. 

This seems to count all visits in the specified range, rather than the number of first visits only.

Any thoughts?

[Anonymous]

I wrote the formula based on your not-entirely-clear description. Please play with the formula (changing it) to see whether you can get what you want. I can't do any better since I don't quite understand your requirements. The best way to get your questions answered quickly and correctly is to read this and stick to it: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Slight tweak and this works, for anyone this may help reading in the future:

change VAR __result to this for first date value rather than all date values in the range:

var __result =
    SUMX(
        VALUES(Customer ID,
        var __firstDate =
            CALCULATE(
                MIN( FT[Metric Date] ),
                KEEPFILTERS( FT[Metric] = "visit" ),
                ALL( 'Date' )
            )

Again, thanks for your help @Anonymous 

[vivran22]

Hello @Anonymous 

Is your issue resolved? If yes, then please mark the appropriate post as a solution. This will help others as well.

If not, please share the details and we can work along to resolve it.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

@Anonymous

Please mark this topic as resolved so that it helps others. Also, sorry but I don't understand your last post. You say to change var __result but it looks like the code you give is no different than the one I wrote apart from the first argument of SUMX which does exactly the same thing as the first argument in my formula. Could you please paste the whole correct code in here. That will really be helpful. Thanks.

[Anonymous]

@Anonymousit's possible I misinterpreted what was being referred to by Customer in SUMX(Customer) and therefore giving the wrong figures.

Full working solution I used:

VAR __startdate = MIN('Date'[Dim date key])

VAR __enddate = MAX('Date'[Dim date key])

VAR __result = SUMX(VALUES('FT'[Customer_id]),

VAR __firstdate = 

CALCULATE(

MIN('FT'[Metric Date]),

KEEPFILTERS('FT'[Metric]="Visit"),

ALL('Date'))

RETURN 

(__firstdate >= __startdate) * (__firstdate <= __enddate))

RETURN

IF(__result, __result)

I hope that's helpful.

[Anonymous]

OK. I took what you posted and ran it through daxformatter. This is exactly why formulas should be correctly formatted. I was reading the raw text you posted without any formatting and I misunderstood it. Please next time when you post code, format it correctly. Thanks.

[Anonymous]

Hi @Anonymous.

The code you pasted makes no sense. Sorry. Have a good look at it. All the calculations in the code are completely useless if you return what you return...

[Anonymous]

@AnonymousI'm not sure it makes them useless as this still works as intended and the figures are reconciling with the database numbers.

However, it is uneccessary, it can be changed to RETURN __result.

If you check your initial post, you'll see that you also posted the same IF statement.

[Anonymous]

Also, your assumptions are correct, re: applicable dimensions on the relvant columns and being used instead of the fact table columns.  For the sake of ease I left them out of the post

[Greg_Deckler]

@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

In your case you would be using COUNTX. The SUMMARIZE should ensure that things are distinct if you group by customer.