Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the following SQL where it will select the lowest proposal price from from a list of proposals based on a set of Enquiries.
For example if I have 10 enquiries with 10 proposals attatched I have 100 proposals. I want to select the min price for each enquiry so ideally I will end up with 10 minimum values for each enquiry.
Here is the sql code which gets exactly what I need.
SELECT distinct(pkenquiries), (SELECT TOP 1 TotalProposalPrice / NULLIF(evp.CoversionRate, 0) FROM EnquiryVenueProposal evp where e.pkEnquiries = evp.EnquiryId and EnquiryVenueProposalStatusId = 1 order by TotalProposalPrice desc) FROM Enquiries e WHERE EnquiryStatusId = 70 and TestEnquiry = 0 group by pkEnquiries
How would I go about creatuing a mesaure in power BI to ideally present with me a list of distinct enquiries that have the minimum proposal price attatched?
I currently have something like
Min Proposal of Lost Enquiries = CALCULATE ( LASTNONBLANK ( 'Enquiries'[pkEnquiries], 1 ), FILTER ( TOPN ( 1, VALUES ( 'EnquiryVenueProposal'[TotalProposalPrice] ), [TotalProposalPrice], DESC ), TRUE () ) )
But this does'nt return the correct data at all?
HI, @shanemc1
You may try to adjust your formula like below:
Min Proposal of Lost Enquiries = CALCULATE ( LASTNONBLANK ( 'Enquiries'[pkEnquiries], 1 ), FILTER ( TOPN ( 1, VALUES ( 'EnquiryVenueProposal'[TotalProposalPrice] ), [TotalProposalPrice], ASC ), TRUE () ) )
or
Min Proposal of Lost Enquiries = CALCULATE ( LASTNONBLANK ( 'Enquiries'[pkEnquiries], 1 ), FILTER ( TOPN ( 1, 'EnquiryVenueProposal', [TotalProposalPrice], ASC ), TRUE () ) )
If it is not your case, please share pbix file or some data sample and expected output . You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |