Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredJoin us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.
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 |
|---|---|
| 8 | |
| 6 | |
| 3 | |
| 3 | |
| 3 |
| User | Count |
|---|---|
| 11 | |
| 9 | |
| 8 | |
| 7 | |
| 6 |