Help in using dax to reference columns that are using direct query

[drogzy]

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

[AlexisOlson · Accepted 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] )

[AlexisOlson · Accepted 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] )

[drogzy]

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

[AlexisOlson · Accepted solution]

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