Remove matrix rows if no value on current year

DAX DAX Calculations
1

Hi,

I want to remove all the rows of matrix if it doesn’t exist in the current year.

Below is the sample data.
image

I was having problems finding the right solutions. Hope someone can help me with this.

2

Hi @JazZ0003

See attached PBIX. Steps are:

  • Create a measure [Year Filter] which returns a 1 if amounts exists for 2021, otherwise blank.

  • Bring this measure into the Filters on this visual and filter for 1

Then Boom all sorted.

If you want this to be more dynamic then you might rework the variable vCurrentYear by maybe working with Max(Dates[Year]). Then if you only had 2019 and 2020 selected the same filtering would occur for 2020.

Hope that helps

Pete
JustCurrentYear.pbix (110.9 KB)

1 Like
3

@BINavPete This works well.

I encountered issue on my own model if I use MAX for dynamics max year. it seems doesn’t work.

Var vCurrentYear = CALCULATE([Sum Amount],Dates[Year]= MAX(Dates[Year]))

I will check it myself for now and update this thread for any progress.

4

Hi @BINavPete,

Tried using MAX on your sample also doesn’t. May I know why?

image
image1789×653 54.5 KB

Checking the MAX measure and it return the right value.
image

But the Filter doesn’t seems to work properly.

5

@JazZ0003

Was more complex than I thought, sorry.

We need to identify the max year selected - variable vMaxYear using ALLSELECTED. Then tweak vcurrentyear for ALLSELECTED too.

Initially when I tried this it still didn’t work. But when I put an ISBLANK(vCurrentYear) into the SWITCH all was fine.
JustCurrentYear.pbix (111.6 KB)

6

@BINavPete

Thank you for giving some of your time to assist me. Really appreciate it.

Upon further testing, your solutions also didn’t work. Replicated it on my project and had the same result.

image
image895×608 36.6 KB

7

Is there any chance you could share your PBIX?

8

Hi @BINavPete,

We have the same model and results as the sample you sent. The above screenshots still show account 112. it should also be hidden.

My actual power bi quite too large though. sorry, might take too much to mask all the data.

9

I’m stumped as to why this isn’t working.
If you bring the YearFilter(max) into the matrix it does work. So the crude solution would be to do that and set the YearFilter(max) column width very small so that is can’t be seen by end user. Need to take autowrap off on col headers here.

It would be good to know why it doesn’t work without Yearfilter(max) in the matrix though. Reckon we need some bigger guns than me here.

@BrianJ , @Greg , @AntrikshSharma - any ideas here. Thought I had this one beaten. But just when you think you’re a DAX master you realise you’re still a minion,

Pete

10

@BINavPete & @JazZ0003 Here are 2 ways to solve this one with measure and other with a calculated column: JustCurrentYear.pbix (113.3 KB)

Measure:

Current Year Has Value = 
VAR MaxYear = 
    CALCULATE ( 
        MAXX ( 
            SUMMARIZE ( Data, Dates[Year] ), 
            Dates[Year] 
        ),
        REMOVEFILTERS ( Dates )
    )
VAR YearIsBlank =
    CALCULATE ( 
        ISBLANK ( [Sum Amount] ),
        Dates[Year] = MaxYear
    )
RETURN IF ( NOT YearIsBlank, [Sum Amount] )

Calculated Column:

Filter Matrix = 
VAR MaxYear = 
    MAXX ( SUMMARIZE ( Data, Dates[Year] ), Dates[Year] )
VAR CurrentYearSum =         
    CALCULATE ( 
        [Sum Amount],
        Dates[Year] = MaxYear,
        ALLEXCEPT ( Data, Data[Account] )
    )
VAR Result = 
    IF ( ISBLANK ( CurrentYearSum ),"Hide", "Show" )
RETURN Result
11

Thank you very much @AntrikshSharmam The calculated column works well. The first solution doesn’t work on my model though.

@BINavPete Thank you for tagging someone. This problem is really beyond my capabilities. I wonder where I belong though if you’re a minion.