topic Re: How do I translate this sql query into DAX and get the visual I want to have? in DAX Commands and Tips

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

Anonymous — Wed, 15 Dec 2021 13:11:14 GMT

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

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

amitchandak — Wed, 15 Dec 2021 13:38:39 GMT

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

measure examples

max(FactOrders[PaymentDateTime])

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

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

Anonymous — Wed, 15 Dec 2021 14:21:39 GMT

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

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

smpa01 — Wed, 15 Dec 2021 15:21:50 GMT

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

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

Anonymous — Wed, 15 Dec 2021 18:11:37 GMT

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

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

smpa01 — Wed, 15 Dec 2021 18:14:30 GMT

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.