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 = ...
  • 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" )
    )
) 

     

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