columns which doesnt have data should not show in matrix table

[Anonymous]

I have requirement that when user selects a quarter in a slicer it should show data of all months for that quater in a matrix table, however it shoud not show the month if ther is no data for any column of any month.

So it should show month of quaters which has data and ignore which doest have data.

In the screenshot below 'Amt V1' doesnt have data so its showing blank for FY2019-02 when user select quater in slicer. my requirement is when situation is like this it should not show FY2019-02  it should show only FY2019-01.

 

in my database i have many quaters in slicer so i cant use visual level filter, i need to get it somehow in dax or other solution. Please help.

I didnt get option to upload my PBI file so i have given tables example below:

Value1: Table

Year Month Qtr Amt V1 Names

FY2019-01	2019-Q1	100	AB1
FY2019-02	2019-Q1
FY2019-01	2019-Q1	160	AB1
FY2019-02	2019-Q1
FY2019-01	2019-Q1	312	AB2
FY2019-02	2019-Q1

Value2: table

Year Month Qtr Names

FY2019-01	2019-Q1	AB1
FY2019-02	2019-Q1	AB2
FY2019-01	2019-Q1	AB1
FY2019-02	2019-Q1	AB1
FY2019-01	2019-Q1	AB2
FY2019-02	2019-Q1	AB2

Names:Table

Names

AB1

AB2

Date: table

Year Month Month

FY2019-01	2019-Q1
FY2019-02	2019-Q1

[v-cherch-msft]

Hi @Anonymous 

It's impossible to hide the blank data with dax in matrix.But i would suggest you hide it manually.Turn off the 'word wrap' first.And then drag the visual and hide the column.

Regards,

[Anonymous]

Thank you v-cherch-msft  for showing how to hide a column.

However, my expectaion is not to hide it. if 'Amt V1' doesnt have values for that month, then it should not show that month in the table, including 'Amt V2' column. 

When we select a single Quater in a slicer, it will show all the month belongs to that quater with all column values. But here i want to ignore the month which doesnt have values for 'Amt V1'  (ignore indlucing 'Amt V2'). i mean complete FY2019-02 to be ignored. 

And show only months which has values for 'Amt V1'  along with other columns. So even Total in the metrix also should  show values of displayed months in the table and not the one ignored.

Expected result as below:

i dont want to select months maullally in the month slicer. it should dynamically ignore the month which doesnt have value for 'Amt V1' 

[v-cherch-msft]

Hi @Anonymous 

It seems you may filter the table in query editor like below.Then it will hide the blank values dynamically.

Regards,

[Anonymous]

I have filtered the blanks in query eidter, but still it showig in the matrix table as blank column. it didnt work.

Is this something we can do using DAX? even i tried some DAX no luck.. please assist.

[kanth452]

Apply 'is not blank' in Visual Level Filters

---

@Anonymous wrote:

---

@Anonymous wrote:

I have filtered the blanks in query eidter, but still it showig in the matrix table as blank column. it didnt work.

Is this something we can do using DAX? even i tried some DAX no luck.. please assist.

---

I have filtered the blanks in query eidter, but still it showig in the matrix table as blank column. it didnt work.

Is this something we can do using DAX? even i tried some DAX no luck.. please assist.

---

[Anonymous]

Hi kanth452,

it didnt work becuase values will be available for 'Amt 2' column for that month,so it will show  'Amt 1' as blanks.

so we need to ignore that  month from the table itself if 'Amt 1' values are not there.

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

It's impossible to achieve that with DAX. But you may get it in Query Editor.Filter the value1 table in query editor as above mentioned.Then group the two tables to get summarized Amt columns.Then merge the two tables for Value1 table.Attached sample file for your reference.

Regards,