cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vbvbvb
Microsoft
Microsoft

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

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?

vbvbvb_0-1613142577277.png

 

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?

 

 

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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

blacf.png

 

cfr.png

blavi.png

Regards

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

3 REPLIES 3
PhilipTreacy
Super User
Super User

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

blacf.png

 

cfr.png

blavi.png

Regards

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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?

@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!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.