Got matrix, displaying columns/values filtered by slicer "Rapportdato" (can hold up to 12 options pr year). Except for 1 column "MEASURE pr YEAR", where I want to count values based on whole year - but still only by using the same slicer.
So, in Query I made a column "RapportdatoAsYear", displaying YYYY, based on column "Rapportdato" (holds only 1 option pr year). Then I made a measure, and put it in as column in my matrix. But it don't work quite as I had hoped... When top IF-code is for the 2023-year, I get correct 2023-data in column "MEASURE pr YEAR". But then I get no data when I change slicer to one of two options for 2022.
If I change measure, and put 2022-year in the top IF-part, I get correct column-data when using both the two slicer options for 2022, but no data when I choose 2023-option. Anyone that can help me out here?
In query "Rapportdato" is formatted as "date", and "RapportdatoAsYear" as "Whole number". Tried change both to text, but no cigar.
MEASURE pr YEAR =
IF(
ISFILTERED(Table1[Rapportdato]) || RIGHT(SELECTEDVALUE(Table1[Rapportdato]), 4) = "2023",
CALCULATE(
COUNT(Table1[RapportdatoAsYears]),
FILTER('Table1', 'Table1'[RapportdatoAsYears] = "2023")
),
IF(
ISFILTERED(Table1[Rapportdato]) || RIGHT(SELECTEDVALUE(Table1[Rapportdato]), 4) = "2022",
CALCULATE(
COUNT(Table1[RapportdatoAsYears]),
FILTER('Table1', 'Table1'[RapportdatoAsYears] = "2022")
),
"0"
)
)
User | Count |
---|---|
103 | |
83 | |
68 | |
47 | |
47 |
User | Count |
---|---|
155 | |
91 | |
82 | |
69 | |
67 |