Forum Discussion
Count data based on slicer parameters
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!
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
3 Replies
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- tamerj1
Community Champion
Hi horaciorek
Please tryIndicatorSeriesCount = 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 IndicatorSeriesWithMultipleYearsCountThanks 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...
