Forum Discussion

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]

       

       

       

       

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

        See below, You may have to compare two things to spot the difference as of now I do not remember what I did 🙂

         

        // DAX Query
        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],
        __DS0FilterTable,
        "SumMETRIC_VALUE", CALCULATE(SUM('Data_Q4'[METRIC_VALUE]))

        )

        VAR __DS0PrimaryWindowed =
        TOPN(
        10000000,
        __DS0Core,
        'Data_Q4'[AREA_GROUP],
        1,
        'Data_Q4'[TYPE],
        1,
        'Data_Q4'[PLANT_GROUP],
        1,
        'Data_Q4'[PLANT_SUB_GROUP],
        1,
        'Date'[YearMonthnumber],
        1,
        'Data_Q4'[SCENARIO_NAME],
        1,
        'Data_Q4'[START_DATE],
        1,
        'Date'[Date],
        1,
        'Data_Q4'[YEAR],
        1
        )

        EVALUATE
        __DS0PrimaryWindowed

        ORDER BY
        '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]

    • Fern_21's avatar
      Fern_21
      Icon for Helper V rankHelper V
      8 months ago

      Hi amalrio !

      How did you solve it? I've a very large dataset in Power BI Report Builder and I need to filter it based on the parameters selected by the user. How can I do that? Thanks