Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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" ) ) )
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" ) ) )
Awesome! Isn't that the way it always works! 🙂
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.
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!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |