Max partition by DAX measure equivalent?

1
votes

In DAX/Power BI, I wondering if it is possible to create an aggregate calculation on a subset of data within a dataset.

I have a listing of customer scores for a period of time, e.g.

date,   customer, score
-----------------------
1.1.17, A,        12
2.1.17, A,        16
4.1.17, B,        10
5.1.17, B,        14

I would like to identify to Max date per customer eg.

date,   customer, score, max date per client
-------------------------------------------
1.1.17, A,        12,    2.1.17
2.1.17, A,        11,    2.1.17
4.1.17, B,        10,    5.1.17
5.1.17, B,        14,    5.1.17

The SQL equivalent would something like-

MAX(date) OVER (PARTITION BY customer).

In DAX/Power BI I realise that a calculated column can be used in combination with EARLIER but this will not be suitable because the calculated column is not responsive to filtering from a slicer. I.e I would like to find the MAX date per client as illustrated above for a filtered date range controlled from a slicer and not for the full data set which is what a calculated column does. Is such a measure possible?

2
sqlpowerbidax

2 Answers

1
votes

You will want a measure like this:

Max Date by Customer = 
    CALCULATE(
        MAX(Table1[Date]),
        FILTER(
            ALLSELECTED(Table1),
            Table1[customer] = MAX(Table1[customer])
         )
     )

The ALLSELECTED removes the local filter context while preserving any slicer filtering.

The filter Table1[customer] = MAX(Table1[customer]) is basically the measure equivalent of Table1[customer] = EARLIER(Table1[customer]) in a calculated column.

0
votes

You can use subquery :

select *, (select max(t1.date) 
           from table t1 
           where t1.customer = t.customer
          ) as max_date_per_client
from table t;