Help with SQL subquery to DAX Please!

[jhouck]

I am having difficulty translating what is a pretty simple SQL query with a subquery to DAX:

select max(rate)rate from
	(select * from Rates r 
		where r.projectID = 'PROJ001' 
		and r.employeeID = 'EMP001'
		and r.date_effective >= '2015-9-1')sub

What I am trying to do is create a calculated column in my model for a time entry fact table looking up the most current rate for an employee on a particular project. I have been able to write the following query using CALULATETABLE(), but I can't seem to figure out how to then select the MAX() value from that calculated table. Here is what I have so far:

EVALUATE
 (
    CALCULATETABLE (
        VALUES ( Rates[rate] ),
        FILTER (
            ALL ( Rates[employeeID] ),
            Rates[employeeID] = "EMP001"
        ),
        FILTER (
            ALL ( Rates[projectID] ),
            Rates[projectID] = "PROJ001"
        ),
        FILTER (
            ALL ( Rates[date_effective] ),
            Rates[date_effective] >= DATEVALUE ( "9/1/2015" )
        )
    )
)

I'm sure there is a way to get my result, probably with a much better method that I have yet to find. Any help from someone who has been here before would be very appreciated!! Thanks 🙂

[jhouck · Accepted solution]

OK, of course right after posting this I land on the solution. Here it is in case some else runs into this post. Of course in my calculated column I'm replacing the string values below with references to my fact table:

EVALUATE
 (
    CALCULATETABLE (
        SUMMARIZE (
            Rates,
            "Rate", MAX ( Rates[rate] )
        ),
        Rates[employeeID] = "EMP001",
        Rates[projectID] = "PROJ001",
        Rates[date_effective] >= DATEVALUE ( "9/1/2015" )
    )
) 

[Greg_Deckler]

Awesome! Isn't that the way it always works! 🙂

[Greg_Deckler]

Check out the technique in this article:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Basically, this uses a MAXX and SUMMARIZE to achieve something similar to what I think you are trying to do.

[jhouck]

I have gotten really close figuring out how to convert this to DAX, but can't get it quite right:

select max(rate)rate from
	(select * from Rates r 
		where r.projectID = 'PROJ001' 
		and r.employeeID = 'EMP001'
		and r.date_effective >= '2015-9-1')sub 

I can recreate a table of results of rates that fit this criteria, but it's the MAX part that I'm stuck on.

I am trying to create a calculated column in a time entry fact table that looks up the rate for the employee. What I have so far, which returns a single colummn table with which I would like to isolate the MAX value from

EVALUATE
 (
    CALCULATETABLE (
        VALUES ( Rates[rate] ),
        FILTER (
            ALL ( Rates[employeeID] ),
            Rates[employeeID] = "EMP001"
        ),
        FILTER (
            ALL ( Rates[projectID] ),
            Rates[projectID] = "PRO001"
        ),
        FILTER (
            ALL ( Rates[date_effective] ),
            Rates[date_effective] >= DATEVALUE( "9/1/2015" )
        )
    )
)

Much appreciation on any answers!!!