Hide column of matrix visual on basis of Blank values

[Jessica_17]

Hello All,
I need your help in hiding column values in matrix visual.

Suppose I have a visual which is like this as shown in the screenshot below. and these columns (CY, PY, %CYA) are in a  single measure as we are using toggle feature to get the desired column name when selected.
Now Client's ask is, when a user selects a year whose previous year do not have data or even a current year do not have data, then its particular quarter should be hidden from the matrix. in short, if anyone from CY or PY or both do not have any value in any row, then that particular quarter should not be shown.

so the output should look like this, similarly need to do for month as well as week too in separte matric tables with same measures.

Can anyone please help me in this, and let me know if anyone need more clarification or example.

[v-yohua-msft]

Hi, @Jessica_17 

I create a sample table:

Create new measures, try the following DAX expression:

%CYA = DIVIDE(SUM('Table'[CY]) - SUM('Table'[PY]), SUM('Table'[PY]), BLANK())

ShowQuarter = 
IF(
    ISBLANK(SUM('Table'[CY])) || ISBLANK(SUM('Table'[PY])) || ISBLANK(DIVIDE(SUM('Table'[CY]) - SUM('Table'[PY]), SUM('Table'[PY]), 0)),
    0,
    1
)

Then put fields in matrix visual:

In the Filters pane, add a visual level filter for ShowQuarter and set it to show only values with a ShowQuarter of 1. Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Jessica_17]

Hi @v-yohua-msft 

Thanks for the help, it works, but there is one issue, if I want to remove only 1 blank measure based column from matrix, it is not working , there also it is removing both after modifying the measure.
Example output for my above doubt is like this

and currently , in dummy data we do not have previous year at all, so it hiding all quarters on top, but it is also removing the left side rows, can we keep the left side rows even if there are no values in any of the column? like the screenshot below?

And these measures I had used it like this, not sure it is because of this or some other reason
Measure Selector = {
("CY Values", NAMEOF('Table'[CY Values_V1]), 0),
("PY Values", NAMEOF('Table'[PY Values_V1]), 1),
("% CYA", NAMEOF('Table'[% CYA_V1]), 2)
}

[v-yohua-msft]

Hi, @Jessica_17 

Based on your information, I create a sample table again:

The data created this time assumes that %CYA has already been calculated.

Then create a new table:

MeasureToggleTable = 
DATATABLE(
    "Measure Name", STRING,
    "Measure ID", INTEGER,
    {
        {"CY Values", 0},
        {"PY Values", 1},
        {"% CYA", 2}
    }
)

Then create new measures, try the following dax:

Measure Selector = 
SWITCH(
    SELECTEDVALUE(MeasureToggleTable[Measure ID]),
    0, SUM('Table'[CY Values]),
    1, SUM('Table'[PY Values]),
    2, SUM('Table'[% CYA]),
    BLANK()
)

ShowQuarter = 
IF(
    ISBLANK(SUM('Table'[CY Values])) || ISBLANK(SUM('Table'[PY Values])) || ISBLANK(SUM('Table'[% CYA])),
    0,
    1
)

You can add a slicer to your report, using the MeasureToggleTable[Measure Name] field. Replace the individual measures (CY Values, PY Values, % CYA) in the matrix visualization with SelectedMeasure, and in the Filters pane, drag the ShowQuarter measure to the filter area at the visualization level. Here is my preview:

If you only want to leave the left column, that's fine

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Jessica_17]

HI @v-yohua-msft 

Thanks for the reply, I think I did not explained well my scenario above, Let me examplain it again.

I am already using a toggle for CY, PY , %CYA measure, where select all is also there, which is used for all visuals which I have.

Now if for particular quarter I do not have CY values,then for that quarter CY Values column should be hidden, rest PY and %CYA should be shown similarly for PY too in the same matrix.

And if none of it is there then the rows should be shown and quarter can be removed and the result should be seen like this

Thanks

[v-yohua-msft]

Hi, @Jessica_17 

Sorry for misunderstanding what you meant.
In the matrix, as long as your columns, for example, Q4 are all empty, it will automatically not display Q4 columns

Similarly, if a column is completely empty when you select a particular piece of data, the matrix will automatically not display that column

So I don't need to do the step of "creating a measure that determines nulls and putting it in a filter" above.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Jessica_17]

Hello @v-yohua-msft 

Thanks for replying, But for slicer control, I was saying all ,measures should be selected and should be seen at the same time when either selected or not, i.e. why the measure is created, and after that the functionality of one whole column values being not there should be hidden accordingly as first scenario.

Thanks,

Jessica

[v-yohua-msft]

Hi, @Jessica_17 

Here's my journey of thought on this issue. Due to the peculiarity of the matrix, if there are three fields in the value, unless the three fields are all empty, then the whole column will be empty, such as the Q4 column in the above reply. If there is another field with a value, then the three field columns in value will exist, just like Q3 above, PY still has a value, but CY and %CYA are all empty and do not disappear.
So the approach I came up with was to combine these three values into one so that when put into the matrix it would show CY, PY and %CYA. I started with a similar approach to you. I create a table that represents three field categories, and then I create the measures. But the problem arises, when using the categories in the newly created table when the slicer selects all, the matrix will not show any values. Next, I try to create parameters. From creating parameters, I found that this is actually very simple, just put the created parameters into the value, and then select all the slicers of the parameters, due to the matrix characteristics, the blank column will automatically disappear. The problem is that the created parameter will not show any value when you put it in value, but if you put it in other rows and columns or other visuals, it will show the values of three fields. So I still need to create a measure, and after creating it, I put it in the matrix and I have the above problem, and the matrix doesn't show any values.
But it's good to have a breakthrough. We can unpivot CY, PY and %CYA columns in PowerQuery.

Then apply and close PowerQuery, put Value into the matrix Value, put the Attribute into the slicer, select them all, and the desired result will appear.

Hope it helps.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Jessica_17]

Hello @v-yohua-msft 

Thanks for the solution. I see you have created a column for CY, PY and %CYA, in my case these are measures, will it work on measures too?

[v-yohua-msft]

Hi, @Jessica_17 

If it's measure, it doesn't work, it needs to be a column. If your CY, PY, and %CYE are all measures, we may need to think of something else. Can you provide some sample data? At the same time, be careful not to divulge your private information

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Jessica_17]

HI @v-yohua-msft 

Sure, here is the sample data
For GC table, we have data like this, from which we are making measure from Value column, some rows for the matrix

For its related table VW, which connected via YearWeek, from which we are taking quarters in matrix column,

KV_MTH_NBR	KV_MONTH_NAME	KV_QUARTER	KV_YEAR	KV_WEEK_NBR	Year_Week	CY_PY	1-2H	Qtr	Week
7	July	3	2022	27	2025W27	CY	2H	Quarter 3	W27
5	May	2	2022	18	2025W18	CY	1H	Quarter 2	W18
6	June	2	2022	22	2025W22	CY	1H	Quarter 2	W22
9	September	3	2022	36	2025W36	CY	2H	Quarter 3	W36
9	September	3	2022	38	2025W38	CY	2H	Quarter 3	W38
12	December	4	2022	50	2025W50	CY	2H	Quarter 4	W50
1	January	1	2022	3	2025W3	CY	1H	Quarter 1	W3
4	April	2	2022	14	2025W14	CY	1H	Quarter 2	W14
4	April	2	2022	16	2025W16	CY	1H	Quarter 2	W16
5	May	2	2022	21	2025W21	CY	1H	Quarter 2	W21
6	June	2	2022	23	2025W23	CY	1H	Quarter 2	W23
6	June	2	2022	26	2025W26	CY	1H	Quarter 2	W26
9	September	3	2022	39	2025W39	CY	2H	Quarter 3	W39
10	October	4	2022	40	2025W40	CY	2H	Quarter 4	W40
11	November	4	2022	47	2025W47	CY	2H	Quarter 4	W47
12	December	4	2022	49	2025W49	CY	2H	Quarter 4	W49
12	December	4	2022	51	2025W51	CY	2H	Quarter 4	W51

and CWL table from which we are confirming the valeus for CY and PY for measure, which also connected with GC via yearweek, 

Year	Week	Quarter	YearWeek	1-2 H	CY_PY	Month
2025	W12	Q1	2025W12	1H	CY	3
2025	W2	Q1	2025W2	1H	CY	1
2025	W5	Q1	2025W5	1H	CY	2
2025	W11	Q1	2025W11	1H	CY	3
2025	W1	Q1	2025W1	1H	CY	1
2025	W49	Q4	2025W49	2H	CY	12
2025	W50	Q4	2025W50	2H	CY	12
2025	W51	Q4	2025W51	2H	CY	12
2025	W52	Q4	2025W52	2H	CY	12

Both table has unidirection relationship with GC table which filters GC table, and here is how the measure looks like

SWITCH(TRUE(),
'GC'[Activation Check] IN {"Display","Price Promotion","Sampling (Retailer)", "SM","RM"},
CALCULATE(SUM('GC'[Value]),'CWL'[Year]= SELECTEDVALUE('CWL'[Year])),
'GC'[Activation Check] IN {"Sampling (Retailer)","CP","HCP","Sampling (National)","National Media"},
CALCULATE(SUM('GC'[Value]),'CWL'[Year] = SELECTEDVALUE('CWL'[Year]),ALL('GC'[Customer])) )

and matrix somewhat looks like this

I have changed some data and name , let me know if I am missing anything.

Thanks, 

Jessica

[v-yohua-msft]

Hi, @Jessica_17 

First of all, I apologize for my misunderstanding of your data. Due to my misunderstanding, the solution question went in the wrong direction. If CY, PY and %CYA are all measures, then our first attempt is correct. For the sake of a better demonstration, I used the original sample data.
First, create a new table, in which only CY, PY, and %CYA represent types.

Measure Selector = DATATABLE (
    "Type",
    STRING,
    {
        {"CY"},
        {"PY"},
        {"%CYA"}
    }
)

To represent that they are all measures, I created corresponding measures for each of the three columns.

After that, create a new measure.

Measure Selector = 
SWITCH(
    SELECTEDVALUE('Measure Selector'[Type]),
    "CY", 'Table'[CY],
    "PY", 'Table'[PY],
    "%CYA", 'Table'[%CYA]
)

Put it into the matrix and at the same time create a slicer that puts the Type column in. This will be displayed when we select the slicer. Here is my preview:

Previously, the matrix didn't hide blank values because my CY, PY, etc. values were columns, and when I put them into the matrix Value, they needed to be displayed even if they were empty. If it's all measures, then we just need to create a measure to merge the three measures, and they will automatically not show blank columns

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Jessica_17]

HI @v-yohua-msft 

Thanks for the soultion, and sorry for not writing it in detail earlier, can we hide those quarters if any of the measure do not have value for that quarter as per original requirement. And also how to Keep  CY, PY , %CYA in this order as screenshot  and somehow %CYA is not showing in % value, even though the data type is % for %CYA measure. And also there was a colour formatting too for each values which is gone now, can we incorporate that again in this?
Example:
If Quarter 2 does not have any value in CY but have value in PY, then Quarter 2 for particular year should not be shown in the matrix like the screenshot below?

Thanks,

Jessica

[Jessica_17]

HI @v-yohua-msft 

Sure, here is the sample data
For GC table, we have data like this, from which we are making measure from Value column, some rows for the matrix

For its related table VW, which connected via YearWeek, from which we are taking quarters in matrix column,

KV_MTH_NBR	KV_MONTH_NAME	KV_QUARTER	KV_YEAR	KV_WEEK_NBR	Year_Week	CY_PY	1-2H	Qtr	Week
7	July	3	2022	27	2025W27	CY	2H	Quarter 3	W27
5	May	2	2022	18	2025W18	CY	1H	Quarter 2	W18
6	June	2	2022	22	2025W22	CY	1H	Quarter 2	W22
9	September	3	2022	36	2025W36	CY	2H	Quarter 3	W36
9	September	3	2022	38	2025W38	CY	2H	Quarter 3	W38
12	December	4	2022	50	2025W50	CY	2H	Quarter 4	W50
1	January	1	2022	3	2025W3	CY	1H	Quarter 1	W3
4	April	2	2022	14	2025W14	CY	1H	Quarter 2	W14
4	April	2	2022	16	2025W16	CY	1H	Quarter 2	W16
5	May	2	2022	21	2025W21	CY	1H	Quarter 2	W21
6	June	2	2022	23	2025W23	CY	1H	Quarter 2	W23
6	June	2	2022	26	2025W26	CY	1H	Quarter 2	W26
9	September	3	2022	39	2025W39	CY	2H	Quarter 3	W39
10	October	4	2022	40	2025W40	CY	2H	Quarter 4	W40
11	November	4	2022	47	2025W47	CY	2H	Quarter 4	W47
12	December	4	2022	49	2025W49	CY	2H	Quarter 4	W49
12	December	4	2022	51	2025W51	CY	2H	Quarter 4	W51

and CWL table from which we are confirming the valeus for CY and PY for measure, which also connected with GC via yearweek, 

Year	Week	Quarter	YearWeek	1-2 H	CY_PY	Month
2025	W12	Q1	2025W12	1H	CY	3
2025	W2	Q1	2025W2	1H	CY	1
2025	W5	Q1	2025W5	1H	CY	2
2025	W11	Q1	2025W11	1H	CY	3
2025	W1	Q1	2025W1	1H	CY	1
2025	W49	Q4	2025W49	2H	CY	12
2025	W50	Q4	2025W50	2H	CY	12
2025	W51	Q4	2025W51	2H	CY	12
2025	W52	Q4	2025W52	2H	CY	12

Both table has unidirection relationship with GC table which filters GC table, and here is how the measure looks like

SWITCH(TRUE(),
'GC'[Activation Check] IN {"Display","Price Promotion","Sampling (Retailer)", "SM","RM"},
CALCULATE(SUM('GC'[Value]),'CWL'[Year]= SELECTEDVALUE('CWL'[Year])),
'GC'[Activation Check] IN {"Sampling (Retailer)","CP","HCP","Sampling (National)","National Media"},
CALCULATE(SUM('GC'[Value]),'CWL'[Year] = SELECTEDVALUE('CWL'[Year]),ALL('GC'[Customer])) )

and matrix somewhat looks like this

I have changed some data and name , let me know if I am missing anything.

Thanks, 

Jessica