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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
valdis
New Member

Convert sql to DAX

Hello team,

I'm new in the world of PowerBI,
Can someone help me convert this SQL req to DAX measure plz?

 

SELECT*,
(SELECT COUNT(*) FROM EDI_SLS_ORDERS_RESPONSE_REPORT AS os WHERE o.OrderNumber=os.OrderNumber) as [Count]
FROM
EDI_SLS_ORDERS_RESPONSE_REPORT AS o
WHERE
(Date_Upset = (SELECT MAX(Date_Upset) AS Expr1
FROM EDI_SLS_ORDERS_RESPONSE_REPORT AS i
WHERE (o.OrderNumber = OrderNumber)))

Details :
I have a table containing duplicate rows and also in this table a row (order id) have multiple statuses (states : Init, accepted, sent etc..)
What I'm looking for is to get the last occurence row ( basing on the insert date) and I want to add a new row to count all the occurrences of this row(order)
I did this in sql but i need to do it in DAX
Regards
Valdis

1 REPLY 1
Greg_Deckler
Super User
Super User

@valdis Generally better if you just post what you are trying to do and sample data. Not everybody knows SQL. But, you your case, this should either be a COUNTX(FILTER...,...) or CALCULATE(COUNT(...), FILTER(...)).

 

Also, welcome and if you have not read these posts I recommend them: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors