Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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.

 

Capture.PNG 

 

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-012019-Q1100AB1
FY2019-022019-Q1  
FY2019-012019-Q1160AB1
FY2019-022019-Q1  
FY2019-012019-Q1312AB2
FY2019-022019-Q1  

 

Value2: table

Year Month Qtr Names

FY2019-012019-Q1AB1
FY2019-022019-Q1AB2
FY2019-012019-Q1AB1
FY2019-022019-Q1AB1
FY2019-012019-Q1AB2
FY2019-022019-Q1AB2

 

Names:Table

Names

AB1
AB2

 

Date: table

Year Month Month

FY2019-012019-Q1
FY2019-022019-Q1

 

Datamodule.PNG

1 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.

1.png

Regards,

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-cherch-msft
Microsoft Employee
Microsoft 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.

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you   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' 

expected.PNG

 

Hi @Anonymous 

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

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

 

Capture.PNG

 

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.

 

Capture.PNG

 




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.

 

Capture.PNG

 


 

Anonymous
Not applicable

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.

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.

1.png

Regards,

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.