cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KIT_LKN
Frequent Visitor

Measure to count rows in 1 column in matrix, based on slicer selection

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"
            ) 
        )

 

KIT_LKN_0-1675512332038.png

 

 

0 REPLIES 0

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors