topic Re: Count data based on slicer parameters in DAX Commands and Tips

Count data based on slicer parameters

horaciorek — Thu, 17 Aug 2023 09:14:19 GMT

Please look at this dashboard just created to illustrate my problem:

First slicer represents the number of years (times) where data exists for an indicator series.

Second slicer represent the year to count from.

For Albania, as i show in the table, the series count should be 3. For Armenia should be 2.

The dashboard is using this data sample:

Country	Year	Indicator	Series
Albania	2015	1.1.1	ind1
Albania	2010	1.1.1	ind1
Albania	2016	1.1.1	ind1
Albania	2018	1.1.1	ind2
Albania	2011	1.1.1	ind2
Albania	2019	1.1.1	ind2
Albania	2015	1.1.1	ind2
Albania	2010	2.1.1	ind3
Albania	2016	2.1.1	ind3
Albania	2018	2.1.1	ind4
Albania	2011	2.1.1	ind4
Albania	2019	2.1.1	ind4
Armenia	2011	1.1.1	ind1
Armenia	2010	1.1.1	ind1
Armenia	2016	1.1.1	ind1
Armenia	2018	1.1.1	ind2
Armenia	2020	1.1.1	ind2
Armenia	2019	1.1.1	ind2
Armenia	2015	1.1.1	ind2
Armenia	2016	2.1.1	ind3
Armenia	2016	2.1.1	ind3
Armenia	2018	2.1.1	ind4
Armenia	2011	2.1.1	ind4
Armenia	2020	2.1.1	ind4
Armenia	2010	2.1.1	ind4

This is my measure:

IndicatorSeriesCount = 
VAR FilteredData =
    FILTER(
        Data,
        Data[Year] >= [Year Value]
    )
VAR IndicatorSeriesCounts =
    SUMMARIZE(
        FilteredData,
        Data[Indicator],
        Data[Series],
        "YearCount", COUNTROWS(FilteredData)
    )
VAR IndicatorSeriesWithMultipleYearsCount =
    COUNTROWS(
        FILTER(
            IndicatorSeriesCounts,
            [YearCount] > [Years Value]
        )
    )
RETURN   IndicatorSeriesWithMultipleYearsCount

I think the problem is that i need to include a distinctcount somewhere, but i am not sure where.

Download the Pbix file here

I'd appreciate any help. Thanks!

Re: Count data based on slicer parameters

tamerj1 — Thu, 17 Aug 2023 10:45:49 GMT

Hi horaciorek
Please try

IndicatorSeriesCount = VAR FilteredData = FILTER( Data, Data[Year] >= [Year Value] ) VAR IndicatorSeriesCounts = SUMMARIZE( FilteredData, Data[Indicator], Data[Series], "@YearCount", COUNTROWS(Data) ) VAR IndicatorSeriesWithMultipleYearsCount = COUNTROWS( FILTER( IndicatorSeriesCounts, [@YearCount] >= [Years Value] ) ) RETURN IndicatorSeriesWithMultipleYearsCount

Re: Count data based on slicer parameters

horaciorek — Fri, 18 Aug 2023 06:51:51 GMT

Thanks for your effort, but it doesn't seem to do the trick. With your measure, using the values I have in the image, it gives me 3 for both countries, when it should be 2 for Armenia...

Re: Count data based on slicer parameters

horaciorek — Fri, 18 Aug 2023 08:41:14 GMT

This measure did the trick:

Filtered = 

VAR FilteredData =

    FILTER(

        Data,

        Data[Year] >= [Year Value]

    )

VAR SummaryTable =

    SUMMARIZE(

        FilteredData,

        Data[Country],

        Data[Indicator],

        Data[Series],

        "YearCount", CALCULATE(DISTINCTCOUNTNOBLANK(Data[Year]))

    )

VAR IndicatorSeriesWithMultipleYearsCount =

    COUNTROWS( FILTER(SummaryTable, [YearCount] >= Years[Years Value]))

RETURN

    IndicatorSeriesWithMultipleYearsCount