Suppressing zero in cards and still able to drill through

Power BI Paginated Reports
1

Hi everyone,

I have a summary page with 18 cards that all have different drill-throughs to report pages. Each have a measure using a count that returns a single value which is used in the drill-through page. It all works correctly however where counts return a zero I get a “Blank” in the card. When I try to test and replace the count with a 0 the drill-though fails a the drill-through page returns all records.

Is there a way I can supress the “Blank” with a zero and be able to drill through retaining only the relevant records.

Thanks,
Simon

2

Answer:

Hi @simonbb,

Using Datamentor/EDNA AI tool built in the EDNA Platform. You will be able to get your questions answered:

Please give DataMentor a try.

Here what comes up in the Datamentor.
Yes, you can suppress the “Blank” with a zero and still retain the drill-through functionality correctly for relevant records. Here’s a step-by-step guide:

Step 1: Create a Custom Measure to Handle BLANK()

Create a custom measure that replaces BLANK() with zero but retains the drill-through context. Use a measure that ensures the original context is preserved.

CustomCountMeasure = 
IF(
    ISBLANK([OriginalCountMeasure]), 
    0, 
    [OriginalCountMeasure]
)

Step 2: Adjust Drill-Through Logic

To ensure the drill-through works correctly, you need to use the original measure for the actual drill-through logic but display the custom measure.

Step 3: Use SWITCH for Conditional Formatting

If necessary, use SWITCH to make sure that zero values are properly formatted:

DisplayValue =
SWITCH(
    TRUE(),
    ISBLANK([OriginalCountMeasure]), 0,
    [OriginalCountMeasure]
)

Step 4: Conditional Formatting in the Card Visual (Optional)

If further customization is needed, apply conditional formatting on the card visual:

  1. Select the Card Visual:
  • Go to the visualization pane.
  • Click ‘Format’ (paint roller icon).
  1. Conditional Formatting:
  • Apply conditional formatting by changing the appearance if the value is 0.

Example of Conditional Formatting:

ColorCode = IF([OriginalCountMeasure] = 0, "Red", "Black")

Step 5: Modify Drill-Through Action

Ensure the drill-through action uses the field tied to the original measure, which helps retain the drill context:

  1. Add the Drill Fields:
  • Ensure that the field you want to use for the drill-through is added to the report and associated properly.
  1. Create a Drill-Through Page:
  • Include the necessary drill-through filters based on the original measure.

Step 6: Save and Test

  • Save your report.
  • Verify that the card visuals display 0 instead of BLANK().
  • Test drill-through functionality to ensure it filters the relevant records correctly.

By following these steps, you can suppress “Blank” with zero and ensure drill-through retains the correct filtering context.

I hope this helps.

Please give DataMentor a try.

Thanks
Keith

3

Thanks Keith for your response,

I tried something like this before but it did not retain the filtering contex.

I did a quick test but it did not seem to work. I will test it again sortly when other work pressures have lifted.

One question: Step 2 Adjust Drill-Trough Logic. I am not sure where this is referring to.
Where is this done?

Thanks,
Simon

4

Please use DataMentor/ENDA AI tool to help you.

This what comes up in DataMentor

To understand this step, let’s break it down:

Key Terms

  • Drill-Through: A feature in Power BI that allows users to click on a data point and “drill through” to a more detailed view.
  • Original Measure: This is the measure initially created that calculates a specific value.
  • Custom Measure: A modified measure that might be used for specific display purposes but not for logic-intensive tasks like drill-through.

Concept Explanation

When you want to create a drill-through experience:

  1. Using the Original Measure:
  • The original measure should be used for the logic that determines what data appears when you drill through. This is because the original measure is typically the core calculation that other measures may depend on.
  1. Displaying the Custom Measure:
  • The custom measure is only used for display purposes in reports where you want to exhibit specific formatting, additional information, or transformations that shouldn’t interfere with drill-through logic.

Example

Let’s assume the following scenario:

  • Original Measure: Total Sales
Total Sales = SUM(Sales[Amount])
  • Custom Measure: Formatted Total Sales
Formatted Total Sales = "$" & FORMAT([Total Sales], "0,0.00")

Drill-Through Setup:

  1. Use the Total Sales measure for the actual drill-through logic:
  • This ensures that when users perform a drill-through action, the underlying calculations remain accurate and consistent.
  1. Display the Formatted Total Sales in reports:
  • While the visual representation in the report uses the custom measure for a better user experience (e.g., formatted as currency), it does not interfere with the drill-through calculation.

Steps to Implement:

  1. Create the Original Measure in your Power BI model.
  2. Create the Custom Measure for display purposes.
  3. Set up Drill-Through using the Original Measure to ensure the detailed data shown reflects accurate calculations.
  4. Use the Custom Measure in your report visuals to enhance readability and presentation.

Summary

To ensure your drill-through works correctly, retain the original measure for the logic and calculations behind the scenes, and use the custom measure purely for display to improve how data is presented in your reports. This approach maintains accuracy in data exploration while enabling enhanced visual presentation.

DataMentor is tool that will be able to help you in your questions/solutions.

thanks
Keith

5

Hi @simonbb - Please share a sample PBIX file to check.

Also, what is the expectation when you say “Is there a way I can supress the “Blank” with a zero and be able to drill through retaining only the relevant records.”

Thanks
Ankit J