Auto hide blank column from power BI matrix

[Chirag4370]

Hello Community !!! @MFelix 

I need help to hide blank valued column from Matrix. as below SS Jun-24 is blank valued, so it should hide from matrix only when I select AL in slicer. Simmilerly there is other Month are blank in KI & TA (Comp). I belive that, Jun-24 is visible because other Comp have values in that month. 

here is data model.

Any kind help would be appreciated.

Link to get PBIX file: https://drive.google.com/file/d/1h6dWiV5biMMF8DE8_qmmNLKGARUJbN_3/view?usp=drive_link

AvgVal : average of values 

%Diff: % difference between curent month average with available prior month

used DAX code for %Diff

%Diff = 
VAR CurrentDate = SELECTEDVALUE('Transaction'[Date])
VAR CurrentCompName = SELECTEDVALUE('Transaction'[Comp])
VAR CurrentArticle = SELECTEDVALUE('Transaction'[Cat])

VAR _last = 
    CALCULATE(
        MAX('Transaction'[Date]),
        FILTER(
            ALL('Transaction'),
            'Transaction'[Comp] = CurrentCompName &&
            'Transaction'[Cat] = CurrentArticle &&
            'Transaction'[Date] < CurrentDate &&
            NOT(ISBLANK('Transaction'[Value])) &&
            'Transaction'[Value] > 0
        )
    )

VAR AvgSoldAmtPrevMon = 
    CALCULATE(
        AVERAGE('Transaction'[Value]),
            FILTER(
                ALL('Transaction'),
                'Transaction'[Comp] = CurrentCompName &&
                'Transaction'[Cat] = CurrentArticle &&
                'Transaction'[Date] = _last &&
                'Transaction'[Value] > 0
            )
    )

VAR AvgSoldAmtCurrMon =
    CALCULATE(
        AVERAGE('Transaction'[Value]),
        FILTER(
            ALL('Transaction'),
            'Transaction'[Comp] = CurrentCompName &&
            'Transaction'[Cat] = CurrentArticle &&
            'Transaction'[Date] = CurrentDate &&
            'Transaction'[Value] > 0 &&
            NOT(ISBLANK('Transaction'[Value]))
        )
    )

VAR Result=
    IF(
        ISBLANK(SELECTEDVALUE('Transaction'[Value])) || SELECTEDVALUE('Transaction'[Value]) = 0,
        BLANK(),
        AvgSoldAmtCurrMon / AvgSoldAmtPrevMon - 1
    )

RETURN IF(NOT(ISBLANK(AvgSoldAmtPrevMon)) && AvgSoldAmtCurrMon > 0 , AvgSoldAmtCurrMon / AvgSoldAmtPrevMon - 1, "")

[MFelix · Accepted solution]

Hi @Chirag4370 ,

Create a table with the values for each measure:

You can change the names of this but then use them on the next measure.

Add the following measure to your data:

Selected measure = 
			SWITCH(
				SELECTEDVALUE('Measure Column'[Measure]),
				"Avg", IF(
					[avg] = BLANK(),
					BLANK(),
				FORMAT(	[avg], "##.00")
				),
				"Diff", IF(
					[%Diff] = BLANK(),
					BLANK(),
					FORMAT(	[%Diff], "##.00%")
				)
			)

Now build your matrix in the following way:

• Rows:
  • Cat
• Columns:
  • Date
  • Measure (new table)
• Values
  • Selected measure (new measure)

Be aware that I also added a measure for  the average value

[MathieuKPC06]

@MFelix Thank you very much for this solution. It is very usefull. 
I allow myself to add that to perform this solution, you need to unselect "show elements without data" in the [Measure] used in the Matrix column.

[Chirag4370]

Thank You @MFelix

You are awesum.

[v-xingshen-msft]

Hi @Chirag4370 ,

Sorry can't view your pbix file due to poilcy limitations. There is no way to automatically hide blank columns in power bi desktop,

you will have to do that yourself!

This is usually the result of a one-to-many relationship that requires a change in data flow direction or a bridge table.

You can also try using show item with no data , here are the solutions I found for you that have been resolved.

Solved: Re: How to remove blank columns from matrix visual... - Microsoft Fabric Community

If that doesn't work, you can try to hide it manually, here are the solutions I found for you that have been resolved.

Solved: Matrix Visual - Filter/Exclude/Hide empty/blank mu... - Microsoft Fabric Community

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.