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

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's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Awesome! Isn't that the way it always works! :)