blank option in slicer with relevant information

[mercedesschil]

Hi

I´m having an issue with a slicer.

I´ve exported to excel tables. One with forecast data, one with actuals. Both of them have a column named PRNPR, which goes for option Payroll and Non-Payroll.

I´ve created a 3rd table in an excel which has two rows: one for Payroll, one for Non-Payroll. Using this 3rd table I made two relationships. one with the forecast table and one with actuals. They are both active.

Then I did a bar graph using forecast and actuals data. I´m now trying to create a slicer so that I can split this information into Payroll and Non-Payroll. The thing is that when I create the slicer (using the 3rd table) I see three options: blank, Payroll and Non-Payroll. If I choose blank the Non-Payroll information for forecast shows up in the bar chart. When I go for Non-Payroll the non payroll actuals appear in the chart. When I select payroll , I can see both Payrolls for forecast and actuals. I´m not sure why is this happening.

I hope I was clear enough and receive a solution for this.

Thanks

Mercedes

[parry2k · Accepted solution]

Did you checked the "Assume Referential Integrity" box from within the relationship between the two tables.  

Other reason could be that your forecast/non forecast contains a value which in your 3rd table which you use for slicer.

There is another thread which talked about this:

http://community.powerbi.com/t5/Desktop/SLICERS-gt-blank-value/td-p/6726

Hope it is helpful.

Thanks,

P

[Anonymous · Accepted solution]

Hi @mercedesschil, are you able to post your .pbix file here?

I've seen this happen when the values are different between the two Fact tables (eg. "Non-Payroll" in one and "Non-payroll" in the other). I'd check that the column you are using to connect the slicer table to the Forecast table actually has the same value. Capitilization and spelling could affect this.

Feel free to post the .pbix here if you'd like us to take a look! 

[Anonymous · Accepted solution]

Hi @mercedesschil, are you able to post your .pbix file here?

I've seen this happen when the values are different between the two Fact tables (eg. "Non-Payroll" in one and "Non-payroll" in the other). I'd check that the column you are using to connect the slicer table to the Forecast table actually has the same value. Capitilization and spelling could affect this.

Feel free to post the .pbix here if you'd like us to take a look! 

[mercedesschil]

Hi JaredK

You were right, it is a spelling error. There is one with the - and another with no -. I believe that´s the problem!

Thanks for making me realize what it was!

[parry2k · Accepted solution]

Did you checked the "Assume Referential Integrity" box from within the relationship between the two tables.  

Other reason could be that your forecast/non forecast contains a value which in your 3rd table which you use for slicer.

There is another thread which talked about this:

http://community.powerbi.com/t5/Desktop/SLICERS-gt-blank-value/td-p/6726

Hope it is helpful.

Thanks,

P

[mercedesschil]

Hi,

I´ve exported two tables in my report. One for forecast, one for actuals. Both with the column PRNPR, which has two options: Payroll and Non-Payroll.

To be able to make a relationship between these two, I´ve created a third table with just two rows, one for Payroll and the other for Non-Payroll. Then created two relationships using this table. One with the forecast and one with actuals. Both are active.

Then I made a bar chart that has the actuals and forecast data. I want a slicer that filter this chart into Payroll and Non-Payroll. I used the 3rd table I´ve created to do the slicer. This slicer, istead of having two option has three: blank, payroll, non payroll. When I stand on blank, I see the non-payroll data for the forecast. When I press non-payroll I see the non-payroll data for actuals. And whe I go to payroll I see the payroll data for both. This is very strange. I hope I was clear enough and I get and answer for this!

Thanks

[austinsense]

See if this helps ... https://community.powerbi.com/t5/Desktop/How-to-rename-a-blank-value-in-a-matrix-row-or-column/m-p/1...

[parry2k · Accepted solution]

Seems like a duplicate thread....answered on other thread