Unable to sort column using "Sort by column" when a filter condition is applied in measure

[D_Khandelwal]

Hello All, 

I've created a below measure (there are 4-5 switch cases but I've shorten it to one only) which is working fine untill and unless I apply "Sort by column" to sort "attribute" column with the help of another "sort" column once I apply sorting then the value of this measure goes blank, I observed the reason behind this behavious is whenever I remove the filter condition ('Data Table'[Attribute] = "PTD_Actual") from measure and sort it then it will work fine but if I keep the filter condition and then sort it then it goes blank, 

It seems like either filter will work or else sorting will work but I need both here, please see the DAX measure and screenshots for reference below

Without sort - please see highlighted content only

With sort

Insights =

var dateV = SELECTEDVALUE(calendarDate[AS_ON_DATE])

Var calc = CALCULATE(

    SUM('Data Table'[Value]),

    TCH_TIME_DIM[AS_ON_DATE]=dateV,

    'Data Table'[Attribute] = "PTD_Actual"

)

Var SwitchV =

SWITCH(

    MAX('Data Table'[Attribute]),

    "Daily-Actual",calc

)

return

SwitchV

 

ThankYou so much in advance
Dinky 

[johnt75]

Inside the CALCULATE try adding REMOVEFILTERS on the column which you are using to sort by.

[D_Khandelwal]

Thanks for your response @johnt75 ,

For sorting I've tried both power query and DAX, the Dax statement is below and I'm not sure how to use REMOVEFILTERS inside calculate as I didn't used calculate function,

Attribute Sort =

SWITCH(

    'Data Table'[Attribute Orig],

    "Daily-Actual",1,

    "Daily-Budget",2,

    "Daily Var.", 3,4

)

Could you please explain it more, 

Thanks,

Dinky 

[johnt75 · Accepted solution]

You need the REMOVEFILTERS in the measure definition, 

Insights =
VAR dateV =
    SELECTEDVALUE ( calendarDate[AS_ON_DATE] )
VAR calc =
    CALCULATE (
        SUM ( 'Data Table'[Value] ),
        TCH_TIME_DIM[AS_ON_DATE] = dateV,
        'Data Table'[Attribute] = "PTD_Actual",
        REMOVEFILTERS ( 'Data Table'[Attribute sort] )
    )
VAR SwitchV =
    SWITCH ( MAX ( 'Data Table'[Attribute] ), "Daily-Actual", calc )
RETURN
    SwitchV

[D_Khandelwal]

It worked, Thank you so much @johnt75 

Also, if you could share the reason behind this behavious that would be awesome.

Thanks again,

Regards,

Dinky

[johnt75]

When you set a column to be sorted by another column, any time the first column is used in a visual the sort by column is also added to the DAX query used to generate the data, even if the sort by column doesn't appear visually. This means that for any given data point both the first column and the sort by column are in the filter context. If you wish to manipulate the filter context to remove or change the first column, you need to take care of the sort by column too.

[D_Khandelwal]

Alright, Got it.
Thanks for your time and solution

Regards,

Dinky