Forum Discussion
columns which doesnt have data should not show in matrix table
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 |
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,
7 Replies
v-cherch-msftMicrosoft Employee
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,
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,