Hide Measure column in Matrix that contains no value

[mbudiman]

hello,

I have 2 tables, Fiscal_Qtr table and Sales Forecast table. Fiscal_Qtr table contains custom financial quarter period, where a field called Qtr_Sequence_No is used to define sequence of Quarter : 0 means current quarter, -1 means last quarter, -2 means last 2 quarter, 1 means next 1 quarter, 2 means next 2 quarter, etc.

There are 5 Measures in Sales Forecast table : 

1. Forecast : is applicable to all Fiscal Quarter
2. Shipped : is applicable to Current and Past Quarters only (Qtr_Sequence_No <= 0)
3. Forecast Variant :  is applicable to Current and Past Quarters (Qtr_Sequence_No <= 0)
4. Scheduled Ship : is applicable to Current and may be Future Quarters (Qtr_Sequence >= 0 )
5. Backlog : is appliable to Current and may be Future Quarters (Qtr_Sequence >= 0)

I want to hide the Measure columns if there are no value for specific fiscal qtr. In this Forecast pbix, current Fiscal Qtr is FY26Q1, next fiscal Qtr is FY26Q2, previous Fiscal Qtr is FY25Q4. 'Shipped' measure and 'Forecast Variant' measure contains no value for future fiscal Quarters (FY26Q2), so these columns should be hidden. 'Schedule Ship' and 'Backlog' measures contains no value for past fiscal Quarters (FY25Q4, FY25Q3, etc). 

forecast.pbix

Question : What is the syntax in Calculation Group to hide Measure column if it contains no value ? Need your advise. Thank you in advance.

forecast.pbix

[danextian]

Field parameters work like pseudo-dimensions that let you switch between measures, but even when a measure has blank values, it will still appear as a separate column. Since your sample PBIX already includes a field parameter, use its field name column as the column header instead of treating it like a measure selector - just ensure to choose Show selected field rather than its values.

Then create this measure:

Display_Column_Value = 
SWITCH (
    SELECTEDVALUE ( Display_column[Display_column Order] ),
    0, [forecast qty],
    1, [Shipped qty],
    2, [variant],
    3, [schedule ship],
    4, [Backlog qty]
)

Please see the attached pbix.

[mbudiman]

hi Danextian,

The solution works 🙂

So I try to recreate new table matrix according to your advise : 

1. Use the Field parameter as column header and set "Show Selected field"

2. I use the measure you had already created.

However, no value is display in the matrix. Do I miss any step ? Could you help me one more time to point out missing step. I've upload the pbix. The new matrix I created is in the "Test" tab. Thank you in advance for your help.

forecast_test.pbix

[danextian · Accepted solution]

You forgot to either expand or go into the next level in the hierarchy. If there isn't a single field of the display column parameter, SELECTEDVALUE returns blank so none in the switch conditions is met. 

[mbudiman]

Got it. Thank you 🙂

[Royel]

Hi @mbudiman You are not allowed to dynamically hide column name in Power BI, you can try with Field Parameters. 

[mbudiman]

hi Royel, I saw a video on using "Calculation Group" to hide a Measure column conditionally. I need help for DAX statement to achieve my problem scenario above.

[lbendlin]

How would that look like in the UI?

You could create a super measure (ie a reference table that lists all the measures, similar to what Field Parameters do) and then return BLANK() as needed which would hide those "columns" - but only if ALL cells are blank for that column/measure.