Forum Discussion

Chirag4370's avatar
Chirag4370
Icon for Helper I rankHelper I
2 years ago
Solved

Auto hide blank column from power BI matrix

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

     

4 Replies

  • 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's avatar
      MathieuKPC06
      New Member

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.