Forum Discussion

NirH_at_BITeam's avatar
NirH_at_BITeam
Frequent Visitor
7 years ago
Solved

Remove blanks data

Hi,   My question is quite simple I guess: Revenus by month for 2 products (Product A ans product B) starting from 2015 till 2018. However, there is no data (= Blank) for 2016 and 2017 (not zero 0...
Data Modeling
Reports
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    7 years ago

    Hi NirH_at_BITeam,

     

    Yes. As we can only drag column field to the Axis bucket of the chart visual, we need to modify table to filter out Axis values which has blank data. 

     

    Best Regards,
    Qiuyun Yu 

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
7 years ago

Hi NirH_at_BITeam

 

Assume the sample raw data like below: 

 

 

We can go to Query Editor and remove the blanks for the table: 

 

 

Or we can create a new table via DAX then use this table data in a chart visual: 

 

Table = SELECTCOLUMNS(FILTER('Table1','Table1'[Revenue]<>BLANK()),"Year",'Table1'[Year],"Revenue",'Table1'[Revenue])
 

 

 

Best Regards,
Qiuyun Yu 

  • NirH_at_BITeam's avatar
    NirH_at_BITeam
    Frequent Visitor
    7 years ago

    Thanks and you were right my data does look like your table.

    So basically there is no simple DAX to resolve this either to handle it in the source/load system (Power Query), or creating the table... is that correct?

     

    NH

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Community Support
      7 years ago

      Hi NirH_at_BITeam,

       

      Yes. As we can only drag column field to the Axis bucket of the chart visual, we need to modify table to filter out Axis values which has blank data. 

       

      Best Regards,
      Qiuyun Yu