topic Auto hide blank column from power BI matrix in DAX Commands and Tips

Auto hide blank column from power BI matrix

Chirag4370 — Fri, 12 Jul 2024 12:05:01 GMT

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

Re: Auto hide blank column from power BI matrix

Anonymous — Mon, 15 Jul 2024 06:33:56 GMT

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.

Re: Auto hide blank column from power BI matrix

MFelix — Mon, 15 Jul 2024 11:09:54 GMT

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

Re: Auto hide blank column from power BI matrix

Chirag4370 — Wed, 17 Jul 2024 10:49:06 GMT

Thank You MFelix

You are awesum.

Re: Auto hide blank column from power BI matrix

MathieuKPC06 — Fri, 08 Nov 2024 18:35:52 GMT

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.