Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 col...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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' 

 

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous 

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

Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      kanth452
      New Member
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.