undefinedHelp with Conditional Formatting in KPI to Show 'No Value Selected' in Power BI

[francesco94_]

Hello everyone,

I am encountering an issue with a KPI indicator in Power BI and I need your help to resolve it. Specifically, I am working on a report that uses a KPI to display the price variation of certain products (seeds and legumes) between one year and the previous year. The goal is to show a custom message ("No value selected") in case data for the current year or the previous year is missing.

Problem Description

Currently, when there is no data for the current year (value field) or for the target price of the previous year (target field), the KPI returns default values like (Blank), 0, or NaN, causing confusion. I would like the KPI to clearly display "No value selected" when data for the current year or the previous year is not available, without showing other values.

Here is a visual example of the problem:

I have attached an image below that shows the issue I am encountering. The part with "(Vuoto)!" and "Anno precedente: 8,0 (-100%)" is the behavior I want to avoid when there are no values. Instead, I would like the KPI to display the screen with "Nessun valore per l'anno selezionato," which means "No value selected for the year."

Fields Used in the KPI

• Value Field: DifferenzialePrezzoSemiLegumi
• Trend Axis Field: Date (year)
• Target Field: PrevisioneDifferenzialeSemiLegumi

  DAX Measures Used

  Main Measure: DifferenzialePrezzoSemiLegumi

  DifferenzialePrezzoSemiLegumi =
  VAR PrezzoVendita =
  CALCULATE(
  DIVIDE(
  SUMX(
  'v_lista_contratti_tot',
  'v_lista_contratti_tot'[prezzo] * 'v_lista_contratti_tot'[qta_ctto]
  ),
  SUM('v_lista_contratti_tot'[qta_ctto])
  ),
  'v_lista_contratti_tot'[A|V] = "Sale",
  'v_lista_contratti_tot'[vecchiaCategoria] = "Seeds and legumes"
  )
  VAR PrezzoAcquisto =
  CALCULATE(
  DIVIDE(
  SUMX(
  'v_lista_contratti_tot',
  'v_lista_contratti_tot'[prezzo] * 'v_lista_contratti_tot'[qta_ctto]
  ),
  SUM('v_lista_contratti_tot'[qta_ctto])
  ),
  'v_lista_contratti_tot'[A|V] = "Purchase",
  'v_lista_contratti_tot'[vecchiaCategoria] = "Seeds and legumes"
  )
  VAR Differenziale = PrezzoVendita - PrezzoAcquisto
  RETURN
  IF(
  ISBLANK(PrezzoVendita) || ISBLANK(PrezzoAcquisto),
  BLANK(),
  Differenziale
  )

  Measure for the Previous Year: PrevisioneDifferenzialeSemiLegumi

  PrevisioneDifferenzialeSemiLegumi =
  VAR DifferenzialeAnnoPrecedente = CALCULATE(
  [DifferenzialePrezzoSemiLegumi],
  DATEADD('TabellaCalendario'[Date], -1, YEAR)
  )
  RETURN
  IF(
  ISBLANK(DifferenzialeAnnoPrecedente),
  BLANK(),
  DifferenzialeAnnoPrecedente
  )

  Measure for Displaying the Status Message: KPI_Display

  KPI_Display =
  IF(
  ISBLANK([DifferenzialePrezzoSemiLegumi]) || ISBLANK([PrevisioneDifferenzialeSemiLegumi]),
  BLANK(), -- Use BLANK to represent "No value selected"
  [DifferenzialePrezzoSemiLegumi]
  )

  Attempts at a Solution

  I have tried using an arbitrary numeric value (e.g., 9999999) to represent missing data and then configured conditional formatting to display "No value selected". However, this approach did not yield the desired result as the KPI still shows default values like (Blank), 0, or NaN.

  Specific Request

  I would like to know if there is a way to configure the KPI so that it displays "No value selected" directly in the indicator when data for the current year (value field) or the previous year (target field) is missing. Specifically, I am interested in:

  1. Examples of DAX code or configurations that can help resolve this issue.
  2. Suggestions on how to manage the display of missing values in KPIs in Power BI without using arbitrary numeric values.

     I appreciate any help or advice you can offer.

     Thank you in advance for your support!

     

[francesco94_]

Hi Yulia Xu,

Thank you for your response. Yes, I want to display "No value selected for the year" in the KPI visualization when there is no data for the target price of the current year (value field) or the previous year (target field).

Example to Illustrate the Issue

Sample Data

Here is a simple dataset:

Date | Category | Type | Price | Quantity
-------------------------------------------------------------
01/01/2023 | Seeds and legumes| Sale | 10 | 100
01/01/2023 | Seeds and legumes| Purchase | 9 | 100
01/01/2022 | Seeds and legumes| Sale | 8 | 100
01/01/2022 | Seeds and legumes| Purchase | 8 | 100

Explanation

In this dataset, if there were no entries for 2023, the KPI would show (Blank), 0, or NaN, which is confusing.

Expected Results

When there is no data for the current year (2023 in this case) or the previous year (2022), the KPI should display "No value selected for the year" instead of (Blank), 0, or NaN. The basis for knowing there is no data is when the KPI shows (Blank) and does not display values in some points.

Desired Outcome

• Current Issue: The KPI shows (Blank), 0, or NaN when there is no data.
• Desired Outcome: The KPI should display "No value selected for the year".

Is it possible to handle missing values in the KPI visualization so that it displays a label indicating "No value selected for the year" when data is missing?

If needed, I can also share a pbix file with the sample data. Please let me know how to proceed.

If it's still unclear, we can organize a call on Thursday so I can share my screen and show you exactly what I mean.

Thank you for your assistance.

Best regards, Francesco

[francesco94_]

Hi Yulia Xu,

Thank you for your response. Yes, I would like to display "No value selected for the year" in the KPI visualization when there is no data for the target price of the current year (value field) or the previous year (target field). The basis for judging that there is no data available is when the KPI value field or target field returns (Blank), 0, or NaN.

Sample Data I have a dataset with the following structure: Date | Category | Type | Price | Quantity 01/01/2023 | Seeds and legumes | Sale | 10 | 100 01/01/2023 | Seeds and legumes | Purchase | 9 | 100 01/01/2022 | Seeds and legumes | Sale | 8 | 100 01/01/2022 | Seeds and legumes | Purchase | 8 | 100

The measures used are:

1. DifferenzialePrezzoSemiLegumi
2. PrevisioneDifferenzialeSemiLegumi
3. KPI_Display

Expected Results When there is no data for the current year or previous year, the KPI should display "No value selected for the year" instead of (Blank), 0, or NaN. Here are the steps I have taken:

DifferenzialePrezzoSemiLegumi =
VAR PrezzoVendita =
CALCULATE(
DIVIDE(
SUMX(
'v_lista_contratti_tot',
'v_lista_contratti_tot'[prezzo] * 'v_lista_contratti_tot'[qta_ctto]
),
SUM('v_lista_contratti_tot'[qta_ctto])
),
'v_lista_contratti_tot'[A|V] = "Sale",
'v_lista_contratti_tot'[vecchiaCategoria] = "Seeds and legumes"
)
VAR PrezzoAcquisto =
CALCULATE(
DIVIDE(
SUMX(
'v_lista_contratti_tot',
'v_lista_contratti_tot'[prezzo] * 'v_lista_contratti_tot'[qta_ctto]
),
SUM('v_lista_contratti_tot'[qta_ctto])
),
'v_lista_contratti_tot'[A|V] = "Purchase",
'v_lista_contratti_tot'[vecchiaCategoria] = "Seeds and legumes"
)
VAR Differenziale = PrezzoVendita - PrezzoAcquisto
RETURN
IF(
ISBLANK(PrezzoVendita) || ISBLANK(PrezzoAcquisto),
BLANK(),
Differenziale
)

PrevisioneDifferenzialeSemiLegumi =
VAR DifferenzialeAnnoPrecedente = CALCULATE(
[DifferenzialePrezzoSemiLegumi],
DATEADD('TabellaCalendario'[Date], -1, YEAR)
)
RETURN
IF(
ISBLANK(DifferenzialeAnnoPrecedente),
BLANK(),
DifferenzialeAnnoPrecedente
)

KPI_Display =
IF(
ISBLANK([DifferenzialePrezzoSemiLegumi]) || ISBLANK([PrevisioneDifferenzialeSemiLegumi]),
BLANK(), -- Use BLANK to represent "No value selected"
[DifferenzialePrezzoSemiLegumi]
)

Screenshots

1. Current Issue: The KPI shows (Blank), 0, or NaN when there is no data

2. Desired Outcome: The KPI should display "No value selected for the year"

If needed, I can also share a pbix file with the sample data. Please let me know how to proceed.

If it's still unclear, we can organize a call on Monday so I can share my screen and show you exactly what I mean.

Thank you for your assistance.

Best regards, Francesco

[v-xuxinyi-msft]

Hi @francesco94_ 

Do you want to display "No value selected for the year" in the KPI visualization when there is no data for the target price of the current year (value field) or the previous year (target field)? What is the basis for judging that there is no data available? Could you please provide some sample data and the expected results based on the sample data? That would be very helpful. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu