cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 BUT blanks).

I'm trying to create a measure that will summarize revenues by product for each year but will show on a chart ONLY the years or month that I have data for while ignoring all the others.

So basically I want to show on a chart only 2015 and 2018 without doing any cherry pick using the visual level filter functionality.

I was trying all kinds of functions in my measure "IF BLANK / ISBLANK / NOT ISBLANK" etc. with no much success. it keeps showing 2015, 2016, 2017 and 2018 where 2016 and 2017 are showing blank.

 

Thasnk!

NH

1 ACCEPTED SOLUTION

Accepted Solutions

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 

Community Support Team _ Qiuyun Yu
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

3 REPLIES 3
Community Support
Community Support

Hi @NirH_at_BITeam

 

Assume the sample raw data like below: 

 

w1.PNG

 

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

 

w2.PNG

 

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])
 
w3.PNG

 

 

Best Regards,
Qiuyun Yu 

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

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

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 

Community Support Team _ Qiuyun Yu
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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors