Forum Discussion
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
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
3 Replies
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 YuThanks 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
