Forum Discussion

D_Khandelwal's avatar
D_Khandelwal
Frequent Visitor
3 years ago
Solved

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

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 
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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

6 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

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

    • D_Khandelwal's avatar
      D_Khandelwal
      Frequent Visitor
      3 years ago

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

        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