Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

Hi,

 

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

lyao716_0-1639573510038.png

lyao716_1-1639573567534.png

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

 

 

5 REPLIES 5
smpa01
Super User
Super User

@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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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

?

@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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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
lyao716_0-1639578017852.pnglyao716_1-1639578100501.png

 

amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.