Slicer (New) how can i show missing value

[User232431]

Hi need help on showing missing value in slicer based on other slice.

I am using slicer (new) to show sum of values by filtering date.

Ex: Date "1/2/2024" has no values of "B" in name and i need to show it by "* No data".

Please find the screen shot below.

Thanks in advance

[Anonymous · Accepted solution]

Hi @User232431 ,

Because Slicer doesn't support measure, you'll need to create a calculated table and calculated columns. This will show "A5" as well as "B*No Data" in the slicer. You also need to create a relationship between the calculated table and the original table in order for the two slicers to interact.

Here is my test and attached pbix file for your reference.

1. Calculated Table.

Table 2 = CROSSJOIN(VALUES('Table'[Date]),VALUES('Table'[Name]))

2. Calculated Column.

SumOfValue = VAR test=CALCULATE(SUM('Table'[Value]),'Table'[Date]=EARLIER('Table 2'[Date])&&'Table'[Name]=EARLIER('Table 2'[Name]))
RETURN if(ISBLANK(test),[Name]&"*No Data",[Name]&": "&test)

3. Relationship.

Many to Many(Table filters Table2)

4. Output.

Best regards,

Mengmeng Li

[Tahreem24]

@User232431 Follow the below steps:

Step1: Create a new table as 

UniqueTable = VALUES(YourTableName[Name])

Step2: Join the Name of this new table with your exisiting table's name

Step3: Then write a new measure as below:

Measure 2 = IF(ISBLANK(SUM(YourTableName[Value])),"* No Data", SUM(YourTableName[Value]))

Step4: Take Multi Row card and put "Name" from Newly added table and above created measure

[User232431]

Thanks for a reply ,  i you can share the pbix file that would be helpful.

[123abc]

To show missing values in a slicer based on another slicer in Power BI, you can use a combination of DAX measures and conditional formatting. Here’s a step-by-step guide to achieve this:

1. Create a Measure to Check for Missing Values:

   • Create a measure that checks if there are any values for the selected date and name. If there are no values, the measure should return “* No data”.

   MissingValueCheck = 
   IF(
       ISBLANK(SUM('YourTable'[Value])),
       "* No data",
       SUM('YourTable'[Value])
   )

2. Add the Measure to Your Visual:

   • Add this measure to the visual where you want to display the data. This will ensure that “* No data” is shown when there are no values for the selected date and name.

3. Use Conditional Formatting:

   • Apply conditional formatting to highlight the “* No data” text. You can do this by setting up rules in the formatting pane of your visual.

4. Sync Slicers:

   • Ensure that your slicers are synced if you are using multiple pages or visuals. This can be done in the “Sync slicers” pane in Power BI.

5. Test Your Setup:

   • Select different dates and names to ensure that the measure correctly identifies when there are no values and displays “* No data”.

This approach will help you dynamically show missing values in your slicer based on the selection of another slicer. If you need more detailed steps or run into any issues, feel free to ask!