DAX / Power BI - Count of Max

1
votes

I have a calculated column which returns the rank of each row by date. Essentially, this column is ranking or "counting" the number of purchases a customer makes (because each time they make a new purchase, a new row is added and that row has a higher rank than their previous purchase.

Purchase Count = RANKX(FILTER(Purchases,Purchases[ID]=EARLIER(Purchases[ID])), Purchases[Date], ,asc)

Result

Note that Alison has 3 purchases and the Purchase Count column counts if it's her 1st, 2nd, or 3rd purchase by Date

enter image description here

What I want is a measure which will essentially count the number of maximum values per customer (which would mean 1, because there is only 1 maximum per customer), but the COUNT function will not accept the MAX function as an input. i.e. COUNT(MAXA(Purchase Count)

What I am trying to develop is something like this:

enter image description here

But the best I can do is this: which is just a simple count of the Purchase Count calculated column (seen above). I want the bar chart to match the "Dynamic Purchases (measure)" column below. In other words, I want the 3 on the x-axis to have a value of 1, because there is only 1 person who has made 3 purchases. I wan the 2 on the x axis to have a value of 0, because there are no customers that have made 2 purchases, and I want the 1 on the x-axis to have a value of 1, because "Billy" is the only customer that has made only 1 purchase. With a simple count, it adds 1 to each column for Alison, because she has made a 1st, 2nd, and 3rd purchase. I ONLY WANT TO COUNT HER MAXIMUM PURCHASE. I WANT TO IGNORE HER 1ST and 2ND PURCHASES

enter image description here

2
maxpowerbidax

2 Answers

1
votes

I think the following might help. I derived the following table using DAX. You might then use Count(Distinct) in the visual's value selection to get what you want from it.

enter image description here

I quickly threw this stacked column chart together with it:

enter image description here

It shows: Steve has one max purchase count of 1; John and Billy both have one max purchase count of 2 each; and Alison has one max purchase count of 3.

The Count (Distinct) setting I mentioned is this:

enter image description here

Here's the DAX code for adding the MaxPerCustomer and CountOfMaxPerCustomerMatches columns:

MaxPerCustomer = if(calculate(max(Table11[Purchase Count]),filter(Table11,Table11[Name]=earlier(Table11[Name])))=Table11[Purchase Count],calculate(max(Table11[Purchase Count]),filter(Table11,Table11[Name]=earlier(Table11[Name]))),BLANK())

and 

CountOfMaxPerCustomerMatches = calculate(COUNT(Table11[MaxPerCustomer]),filter(Table11,Table11[MaxPerCustomer]=EARLIER(Table11[MaxPerCustomer])))

Edit addendum follows: (This addendum is in response to your comment)

You might then also create a new table from what was done above...

enter image description here

...using Table = GROUPBY(Table11,Table11[MaxPerCustomer],"CountOfMaxPerCustomerMatchesGrouped",countx(CURRENTGROUP(),Table11[MaxPerCustomer])), which will give you this:

enter image description here

And then use that new table for a visual:

enter image description hereenter image description here

0
votes

The solution that I came up with might not be the most elegant, but it gets around calculated columns and tables not taking slicers into consideration.

1) I created a table in Power BI that was just integers by using the CALENDAR function.

Counts = SELECTCOLUMNS(
    CALENDAR(DATE(1900, 1, 2), DATE(1900, 1, 6)), 
    "Count", DATEDIFF(DATE(1900, 1, 1), [Date], DAY)
)

Count Table

2) I crossjoined that Counts table to the original table and selected only the necessary columns.

PurchaseCounts = CROSSJOIN(
    SELECTCOLUMNS(Purchases, 
        "Date", Purchases[Date], 
        "ID", Purchases[ID]
    ),
    Counts
)

Crossjoin Table

3) In that crossjoined table, I added the desired measure.

CustomerCount = CALCULATE(
    DISTINCTCOUNT(PurchaseCounts[ID]),
    FILTER(
        GROUPBY(PurchaseCounts, 
            PurchaseCounts[ID], PurchaseCounts[Count], 
            "PurchaseCount", COUNTX(CURRENTGROUP(), PurchaseCounts[Date])
        ),
        PurchaseCounts[Count] = [PurchaseCount]
    )
)

Measure Formula

4) I made some quick visuals and a slicer based of the crossjoined table. When filtering by date, the histogram shows the expected values.

Results