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.
Hi all,
I understand when referencing coulmns while using dax you have to insert SUM, MIN, MAX, AVERAGE in the measure but how does that work when you have text or dates.
I am trying to create a measure where all my columns are in one table.
Adding a custom column in the power query would not wok for this case.
For example table is called: 'Table1'; My columns are called: [Job] in text format, [Start] in date format,
I want to create measures such as: IF 'Table1'[Job] = "Field" then 'Table1'[Start] else null
Looking to see if anyone can help in suggesting the right measure to include before the table reference so it would capture the whole column.
Thanks
Solved! Go to Solution.
You can use MAX or MIN on date fields and text fields (it uses alphabetical sorting) but I usually prefer to use SELECTEDVALUE for text like this:
IF (
SELECTEDVALUE ( Table1[Job] ) = "Field",
MAX ( Table1[Start] )
)
However, DirectQuery can handle simple calculated columns like this just fine whether you add them in the query editor as a custom column (if [Job] = "Field" then [Start] else null) or as a calculated column in DAX: IF ( Table1[Job] = "Field", Table1[Start] )
A couple of possibilities:
IF (
SELECTEDVALUE ( Table1[Job] ) IN { "Field", "Office" },
MAX ( Table1[Start] )
)
or
VAR _Job = SELECTEDVALUE ( Table1[Job] )
RETURN
IF (
_Job = "Field" || _Job = "Office"
MAX ( Table1[Start] )
)
You can use MAX or MIN on date fields and text fields (it uses alphabetical sorting) but I usually prefer to use SELECTEDVALUE for text like this:
IF (
SELECTEDVALUE ( Table1[Job] ) = "Field",
MAX ( Table1[Start] )
)
However, DirectQuery can handle simple calculated columns like this just fine whether you add them in the query editor as a custom column (if [Job] = "Field" then [Start] else null) or as a calculated column in DAX: IF ( Table1[Job] = "Field", Table1[Start] )
Thank you, this works.
What would the code look like if you had more than 1 if condition?
Ex: IF ( SELECTEDVALUE ( Table1[Job] ) = "Field" or "Office", Max (Table1[Start]) )
Thanks
A couple of possibilities:
IF (
SELECTEDVALUE ( Table1[Job] ) IN { "Field", "Office" },
MAX ( Table1[Start] )
)
or
VAR _Job = SELECTEDVALUE ( Table1[Job] )
RETURN
IF (
_Job = "Field" || _Job = "Office"
MAX ( Table1[Start] )
)
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |