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
Oded-Dror
Helper III
Helper III

TSQL GroupBy with Having

Hi there,

How do I convert TSQL to DAX
Select CustomerID
From Customer
Where CreatedDT >= 1/1/2022 And CreatedDT <= 1/31/2022
Group by CustomerID
Having Max(NewCustomerID) = 1  ---------- This is another column

 

In DAX I did
Filter(
    GroupBY(
            Customer,
            CreateDT
            NewCustomerID
       )
, Customer[CreatedDT] >= 20220101 && Customer[CreatedDT] <= 20220131 &&
Customer(NewCustomerID) = 1 ------ DAX not allowed MAX(Customer[NewCustomerID) = 1
)
The result are not correct

How do I translate it to DAX?
Thanks,

Oded Dror

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

Table = 
CALCULATETABLE(
    FILTER(
        DISTINCT( Customer[CustomerID] ),
        CALCULATE( MAX( Customer[NewCustomerID] ) ) = 1
    ),
    EOMONTH( Customer[CreatedDT], 0 ) = DATE( 2022, 1, 31 )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

Table = 
CALCULATETABLE(
    FILTER(
        DISTINCT( Customer[CustomerID] ),
        CALCULATE( MAX( Customer[NewCustomerID] ) ) = 1
    ),
    EOMONTH( Customer[CreatedDT], 0 ) = DATE( 2022, 1, 31 )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

You are Super Duper - It works
Thanks,

Oded Dror

One more question how do I display this value in a matrix for each row?

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.