Forum Discussion

jhouck's avatar
jhouck
Frequent Visitor
8 years ago
Solved

Help with SQL subquery to DAX Please!

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's avatar
    jhouck
    8 years ago

    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" )
    )
) 

     

4 Replies

  • jhouck's avatar
    jhouck
    Frequent Visitor
    8 years ago

    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" )
    )
) 

     

  • jhouck's avatar
    jhouck
    Frequent Visitor
    8 years ago

    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!!!