Forum Discussion

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

amalrio , move filter after the summarized column

 

 

DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Data_Q4'[PLANT_SUB_GROUP])),
NOT('Data_Q4'[PLANT_SUB_GROUP] IN {"OTHER"})
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
'Data_Q4'[AREA_GROUP]
,'Data_Q4'[TYPE]
,'Data_Q4'[PLANT_GROUP]
,'Data_Q4'[PLANT_SUB_GROUP]
,'Date'[YearMonthnumber]
,'Data_Q4'[SCENARIO_NAME]
,'Data_Q4'[START_DATE]
,'Date'[Date]
,'Data_Q4'[YEAR]

,"SumMETRIC_VALUE", CALCULATE(SUM('Data_Q4'[METRIC_VALUE]))

,__DS0FilterTable // at the end of summarizecolumns //https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax
//, RSCustomDaxFilter(@DataQ4SCENARIONAME,EqualToCondition,[Data_Q4].[SCENARIO_NAME],String)

//,RSCustomDaxFilter(@DataQ4YEAR,EqualToCondition,[Data_Q4].[YEAR],Double)

//,RSCustomDaxFilter(@DataQ4AREAGROUP,EqualToCondition,[Data_Q4].[AREA_GROUP],String)
)

amalrio's avatar
amalrio
Icon for Helper V rankHelper V
5 years ago

It is after Summarize columns Champ.. am I missing a plot, my query above has the RSCustomDaxFilter at the end of the summarizie columns ??

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hey amalrio,

    Did you figure out ?

     

    Thanks

    • amalrio's avatar
      amalrio
      Icon for Helper V rankHelper V
      5 years ago

      Anonymous , Yes I manged.  🙂

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thats nice... Could you please post your solution .. 

        I also need to add filter in Dax query.

         

        This is my Dax query and I need filter on PL Group and Month as well

         

        Thanks in advance .

         

        // DAX Query
        DEFINE
        VAR __DS0Core =
        SUMMARIZECOLUMNS(
        ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('LCS GL-Data'[PL Group], 'LCS GL-Data'[PL Item Description]), "IsGrandTotalRowTotal"
        ),
        "Total_Actual", 'LCS GL-Data'[Total Actual],
        "PreviousYearCalc", 'LCS GL-Data'[PreviousYearCalc],
        "MTD_Variance", 'LCS GL-Data'[MTD Variance]
        )

        VAR __DS0PrimaryWindowed =
        TOPN(
        502,
        __DS0Core,
        [IsGrandTotalRowTotal],
        0,
        'LCS GL-Data'[PL Group],
        0,
        'LCS GL-Data'[PL Item Description],
        1
        )

        EVALUATE
        __DS0PrimaryWindowed

        ORDER BY
        [IsGrandTotalRowTotal] DESC,
        'LCS GL-Data'[PL Group] DESC,
        'LCS GL-Data'[PL Item Description]