Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
@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
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.
This is what I wrote:
@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] )
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |