Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |