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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
vbvbvb
Employee
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?

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.