Forum Discussion

drogzy's avatar
drogzy
Icon for Helper I rankHelper I
4 years ago
Solved

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

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

DAX
DAX Calculation
LOOKUPVALUE
measure
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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's avatar
    AlexisOlson
    4 years ago

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

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      drogzy
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

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