Forum Discussion
Helper IIDimension Table Yes/No Slicer - Issue with blank rows
Hi,
I have a Dimension Table "Budget Lines", where I have added a calculated column "Filter Out Budget Line Z?" which is a simple IF statement: If Budget Line is Z, Return "No", Else Return "Yes".
This gives a Dimension table that looks like this:
| Budget Line | Filter Out Budget Line Z? |
| A | Yes |
| B | Yes |
| C | Yes |
| D | Yes |
| E | Yes |
| Z | No |
This allows me to add a basic slicer to my reports (see below). If the user selects "Yes", then Budget Line Z is filtered out.
The problem I am having is that there are Transactions posted with a blank Budget Line, and I cannot add a Blank row to my dimension table because it is a key table. This means that when the user selects "Yes" in the Slicer "Filter our Budget Line Z?" then the slicer filters out Budget Lines "Z" and blank Budget Lines. It should not filter out blank Budget Lines, only Budget Line Z.
Any suggestions on a different way I can fix this? Thank you for reading.
LostintheBIu You could use an independent table for your slicer, just Yes and No values and then create a measure that you put in your visual level filter that filters out the Z budget lines only.
7 Replies
- MattiaFratello
Super User
Hi LostintheBIu, in Power Query replace blank with a default value of your choice and then add this value to the dimension as well
If this helps please feel free to mark it as solution and to give kudos 👍
LostintheBIuThanks for the reply. Good idea but unfortunately I cannot replace the blank values in the fact table, because this is accounting/financial data, so the raw transaction data cannot be adjusted from how it is in our ERP system.
- GeraldGEmerick
Memorable Member
LostintheBIu You could use an independent table for your slicer, just Yes and No values and then create a measure that you put in your visual level filter that filters out the Z budget lines only.
- LostintheBIu
Thanks for the reply. This sounds promising. Please could you give an example of how the measure could be written?
- LostintheBIu
Nevermind, Chat GPT helped write me a formula! Thanks for the idea 🙂
- Chewdata
Responsive Resident
Hello,
It is best to replace the blank/null values in the facttabel with a default value, like "Unknown" or "Missing" and add that default value to the Dimension table.- LostintheBIu
Thanks for the reply. Good idea but unfortunately I cannot replace the blank values in the fact table, because this is accounting/financial data, so the raw transaction data cannot be adjusted from how it is in our ERP system
