Blank values for Data slicer using only SPO list as data source

[vbvbvb]

Hi all,

PBI noob here - i have PBI which is using columns from SharePoint Online as data source to produce visuals.

It's Data Slicer + Filter using SPO columns and outcome is  heavily dependent on multiple filters used, so question is -  is it possible to show these blanks as 0's?

I have seen many posts about using Measures or DAX but all solutions are aiming at different type of data - i can't see what is main difference when SPO is used as data source ? There are no Measures, no forumlas etc, there's just data from SPO in form of table and columbs and then Data Slicer with applied filters - and if combination of these filters return no results, it shows (Blank)'s.

Should Measure with If ... IsBlank to be addeed to Filter as data field? Or there's another way to make sure that every time combination fo filters would produce (Blank) it would turn into 0's instead?

[PhilipTreacy · Accepted solution]

Hi @vbvbvb 

Download sample PBIX with the following visuals and data

Presumably these cards are displaying things like Count or Sum of some columns?  You could write measures to do the same and specify 0 if the result is Blank e.g.

Measure = 

VAR _Count = CALCULATE(COUNTROWS('Example Data'), FILTER('Example Data', 'Example Data'[Scenario ] = "No Customer Name"))

RETURN

IF(ISBLANK(_Count), 0, _Count)

Or you can set Conditional Formatting on the cards to make the text white when the value is (Blank).

Regards

Phil

[vbvbvb]

Hi @PhilipTreacy ,

Thanks for checking, I don't know what core operation is performed here on each data slicer - either it's sum or count etc, because it's not visible and I don't know where to check that. From UI point of view, it looks like - drag and drop Data Slicer, select column you need from imported SPO List Data to act as Filter - that's it.

If I understood your advice correctly, only way to work around that is to 'mimic' same functionality with new measures for each of these data slicers?

[vbvbvb]

@PhilipTreacy ,

So I tried to use your suggested measure syntax, it works fine - I just had to use more && and || for multi-filters and instead of Data Slicent, to use 'Card' visual - but result with these measures is 1:1  to the Data Slicer. Thanks a lot!