How do I translate this sql query into DAX and get the visual I want to have?

[Anonymous]

Hi,

I want to make such a visual. % of customers in different Product.  I have product hierarchy and customer IDs. 

However, Customer IDs is from our order table. Which means it is not necessary the lastest purchase that the customer made. I want to have the lastest product they purchased and see how that distributed in different groups.
I have a sql query like this:

select max(PaymentDateTime),Key_Profiles, Key_ProductDetails
from [edw].[FactOrders]
group by Key_Profiles,Key_ProductDetails
order by key_profiles

How can I create a measure that filter only the lastest purchase? 

Thank you very much for helping.

BR,

Lin

[smpa01]

@Anonymous  the equivalent measure is

Measure = calculate(max(tbl[PaymentDatetIme]),allexcept(tbl,tbl[Key_Profiles],tbl[Key_ProductDetails]))

once you have that, put Key_Profiles, Key_ProductDetails on X axis and use the measure on Y axis

[Anonymous]

Thank you very much. I will try it later. I actually don't need Key_ProductDetails from order table. This is the foreign key that is connected with primary key in the Table called 'product'.  And that's where Axis field come from. I am using product name hierarchy from 'Product' table. So in this way,  do I need to change your formula? like

Measure = calculate(max(tbl[PaymentDatetIme]),allexcept(tbl,tbl[Key_Profiles],'product'[Key_ProductDetails]))

?

[smpa01]

@Anonymous  I am having trouble visualizing the req. Is it kindly possible to cretae a pbix in a samll scale and post here or provide a SQL DDL.

I was simply translating the query.

[Anonymous]

This is what I wrote:

Customer_Latest_Purchase =

CALCULATE(

count('edw FactOrders'[Key_Profiles]),

Filter('edw FactOrders','edw FactOrders'[PaymentDateTime] = max('edw FactOrders'[PaymentDateTime]))

)
But the number is not correct

[amitchandak]

@Anonymous , Max on the column  PaymentDateTime in a visual should do

measure examples

max(FactOrders[PaymentDateTime])

or

maxx(summarize(FactOrders, [Key_Profiles], [Key_ProductDetails], "_1", max(FactOrders[PaymentDateTime])  ) , [_1] )