Create new column based on specific row value grouped by ID in DirectQuery

[Anonymous]

I have a table similar to to what is shown below in DirectQuery. I want to create a new column with NewDate grouped by ID. Do note Response column is of string type but new column would be of Date type. Thanks in advance!

Date	|ID	|Question	|Response
10/31/2022	|001	|Role	|Manager
10/31/2022	|001	|Rate	|20
10/31/2022	|001	|NewDate	|06/15/2022
11/1/2022	|002	|Role	|Server
11/1/2022	|002	|Rate	|10
11/1/2022	|002	|NewDate	|07/15/2022

Date	|ID	|Question	|Response	|NewDate
10/31/2022	|001	|Role	|Manager	|6/15/2022
10/31/2022	|001	|Rate	|20	|6/15/2022
10/31/2022	|001	|NewDate	|06/15/2022	|6/15/2022
11/1/2022	|002	|Role	|Server	|7/15/2022
11/1/2022	|002	|Rate	|10	|7/15/2022
11/1/2022	|002	|NewDate	|07/15/2022	|7/15/2022

[Anonymous]

Hi @Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 measures.

_1 = IF(MAX('Sheet1$'[Question])="Newdate",MAX('Sheet1$'[Response]),BLANK())

Measure= MAXX(FILTER(ALL('Sheet1$'),'Sheet1$'[ID]=SELECTEDVALUE('Sheet1$'[ID])),[_1])

If it still does not help, please provide your pbix file without privacy information for us testing.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous 

Thanks for replying. I just got blank values returned since my table is a lot more complex than the example I provided. It is using direct query which wouldnt be useful if I share the pbix file?

[tamerj1]

Hi @Anonymous 

Please try

NewDate =
DATEVALUE (
    MAXX (
        FILTER (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
            NOT ISERROR ( DATEVALUE ( 'Table'[Response] ) )
        ),
        'Table'[Response]
    )
)

[Anonymous]

Hi @tamerj1 . Thanks for your response.

I do get the error 'Function MAXX is not allowed as part of calculated column dax expressions on direct query models'. Maybe using SELECTCOLUMNS function?

[tamerj1]

@Anonymous 

Yes indeed. I hate direct query. Please try

NewDate =
DATEVALUE (
    SELECTCOLUMNS (
        FILTER (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
            NOT ISERROR ( DATEVALUE ( 'Table'[Response] ) )
        ),
        "@NewDate", 'Table'[Response]
    )
)

[Anonymous]

@tamerj1 

Same Error: Function 'SELECTCOLUMNS' is not allowed as part of calculated column DAX expressions on DirectQuery models.

Then created a seperate table with duplicate values and got 'A table of multiple values was supplied where a single value was expected.'

[tamerj1]

@Anonymous 

That means you have multiple dates per ID try to include [Date] with the ALLEXCEPT 

NewDate =
DATEVALUE (
    MAXX (
        FILTER (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Date] ) ),
            NOT ISERROR ( DATEVALUE ( 'Table'[Response] ) )
        ),
        'Table'[Response]
    )
)

[Anonymous]

@tamerj1 

hmm I am simply getting [Date] again.

[tamerj1]

@Anonymous 
Not possible. Please paste a screenshot of the dax code

[Anonymous]

@tamerj1 

I just replaced Date with SubmittedDate, ID with SubmissionID, and Response with ResponseText. I find it very weird too how it grabs the submitteddate 

DATEVALUE (
    MAXX (
        FILTER (
            CALCULATETABLE ( 'CI_FormAll Solar', ALLEXCEPT ( 'CI_FormAll Solar', 'CI_FormAll Solar'[SubmissionID] ,'CI_FormAll Solar'[SubmittedDate] ) ),
            NOT ISERROR ( DATEVALUE ( 'CI_FormAll Solar'[ResponseText] ) )
        ),
        'CI_FormAll Solar'[ResponseText]
    )
)

[tamerj1]

@Anonymous 
Just a trial, please use the same code without NOT and the outer DATEVALUE

=
MAXX (
    FILTER (
        CALCULATETABLE (
            'CI_FormAll Solar',
            ALLEXCEPT (
                'CI_FormAll Solar',
                'CI_FormAll Solar'[SubmissionID],
                'CI_FormAll Solar'[SubmittedDate]
            )
        ),
        NOT ISERROR ( DATEVALUE ( 'CI_FormAll Solar'[ResponseText] ) )
    ),
    'CI_FormAll Solar'[ResponseText]
)

[Anonymous]

@tamerj1 

Same result, well now it is not datetime type but still submitteddate date-type

[tamerj1]

@Anonymous 

Should be something related to direct query. Or perhaps I'm missing something. I should seek help from the best in the field @Greg_Deckler  @amitchandak