Skip to content

Instantly share code, notes, and snippets.

@prateeka
Last active July 5, 2021 19:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save prateeka/cb2bcb6527bdacc121e544873dcdf517 to your computer and use it in GitHub Desktop.
Save prateeka/cb2bcb6527bdacc121e544873dcdf517 to your computer and use it in GitHub Desktop.
DistinctCount additionDistinctCount addition
case MdxFunctions.DistinctCount.Applied(valueSet :: HNil) =>
val resolvedVal = ConstantValue(5, IntType)
ComplexAggregatedValue(resolvedVal, resolveSetExpression(valueSet), core.DistinctCountExact)
@prateeka
Copy link
Author

prateeka commented Jul 5, 2021

for atscale-3271, made the above additions and testing with:

WITH
MEMBER Measures.TestAvg AS
  DISTINCTCOUNT(
    {
      DESCENDANTS([DateCustom].[Retail445].currentMember, [DateCustom].[Retail445].[Reporting Quarter], SELF)
    }
  )
SELECT 
{[Measures].[TestAvg]} ON COLUMNS,
NON EMPTY 
Hierarchize(
		DrilldownMember(
    {
      DrilldownMember(
      {
        DrilldownLevel(
        {[DateCustom].[Retail445].[All]},,,
        INCLUDE_CALC_MEMBERS
        )
      }, 
      {[DateCustom].[Retail445].[Reporting Year].&[2008-01-06T00:00:00]},,,
      INCLUDE_CALC_MEMBERS
      )
    }, 
    {
      [DateCustom].[Retail445].[Reporting Half Year].&[2008-01-06T00:00:00],
      [DateCustom].[Retail445].[Reporting Half Year].&[2008-07-06T00:00:00]
    },,,INCLUDE_CALC_MEMBERS
  )
) ON ROWS  
FROM [ASAdventureSmall] 
WHERE ([DateCustom].[Reporting Year2].[Reporting Year2].&[2008-01-06T00:00:00]) 

is always giving the distinct count as for all elements of result set. This is happening as distinct count is incorrectly working on the Constant value above (which is 5 but was earlier 1 but for investigation changed it to 5) instead of 'reporting quarter' . Attaching a sample sql for your preview:

SELECT
    MIN(t_90.c_41) AS c0,
    t_90.c1 AS c1,
    t_90.c2s0 AS c2s0,
    t_90.c3s0 AS c3s0
FROM
    (
        SELECT
            MIN(t_89.c_41) AS c_41,
            t_89.c1 AS c1,
            t_89.c2s0 AS c2s0,
            t_89.c3s0 AS c3s0
        FROM
            (
                SELECT
                    COUNT(DISTINCT t_87.c_41) AS c_41,
                    COALESCE(t_88.c_42, t_87.c1) AS c1,
                    COALESCE(t_88.c_60, t_87.c2s0) AS c2s0,
                    COALESCE(t_88.c_44, t_87.c3s0) AS c3s0,
                    COALESCE(t_88.c_52, t_87.c_55) AS c_55
                FROM
                    (
                        SELECT
                            t_82.c_60 AS c_60,
                            t_82.c_59 AS c_61,
                            t_84.c_52 AS c_52,
                            t_84.c_42 AS c_42,
                            t_84.c_44 AS c_44
                        FROM
                            (
                                SELECT
                                    datecustom_t81.reporting_year AS c_60,
                                    datecustom_t81.reporting_year AS c_59
                                FROM
                                    as_adventure_small.datecustom AS datecustom_t81
                                WHERE
                                    true
                                GROUP BY
                                    1,
                                    2
                            ) AS t_82
                                JOIN
                            (
                                SELECT
                                    datecustom_t83.reporting_year AS c_62,
                                    datecustom_t83.reporting_year AS c_52,
                                    datecustom_t83.reporting_year AS c_43,
                                    datecustom_t83.reporting_year_name AS c_42,
                                    datecustom_t83.reporting_year AS c_44
                                FROM
                                    as_adventure_small.datecustom AS datecustom_t83
                                WHERE
                                    true
                                GROUP BY
                                    1,
                                    2,
                                    3,
                                    4,
                                    5
                            ) AS t_84
                            ON
                                    t_82.c_60 = t_84.c_43
                        GROUP BY
                            1,
                            2,
                            3,
                            4,
                            5
                    ) AS t_88
                        JOIN
                    (
                        SELECT
                            5 AS c_41,
                            datecustom_t85.reporting_year_name AS c1,
                            datecustom_t85.reporting_year AS c2s0,
                            datecustom_t85.reporting_year AS c3s0,
                            datecustom_t85.reporting_year AS c_70,
                            datecustom_t85.reporting_year AS c_55,
                            CAST(FORMAT_TIMESTAMP("%E4Y", datecustom_t85.Reporting_Year) AS Int64) AS c_56,
                            datecustom_t85.reporting_quarter_of_year AS c_57
                        FROM
                            as_adventure_small.datecustom AS datecustom_t85
                        WHERE
                            true
                        GROUP BY
                            2,
                            3,
                            4,
                            5,
                            6,
                            7,
                            8
                    ) AS t_87
                    ON
                            t_88.c_60 = t_87.c_70
                WHERE
                        COALESCE(t_88.c_52, t_87.c_55) = '2008-01-06 00:00:00.000000'
                GROUP BY
                    2,
                    3,
                    4,
                    5
            ) AS t_89
        GROUP BY
            2,
            3,
            4
    ) AS t_90
GROUP BY
    2,
    3,
    4
ORDER BY
    2 ASC

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment