Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vbvbvb
Microsoft Employee
Microsoft Employee

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?

vbvbvb
Microsoft Employee
Microsoft Employee

@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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors