Search code examples
powerbidaxpowerbi-desktop

How to make make dynamic title with no, single and multi selection of slicer values in Power BI?

I have a table named Sheet1. It has three columns, RadioFinalizedBy, ScanCompleteDate, and, RadioFinalisedDate. RadioFinalizedBy has names in it.

RadioFinalizedBy ScanCompleteDate RadioFinalisedDate
aaa 01-01-2024 02-02-2024
bbb 02-01-2024 03-02-2024
ccc 03-01-2024 04-02-2024
ddd 04-01-2024 04-02-2024

I want to make a dynamic title with RadioFinalizedBy in Slicer. The dynamic title should show 'Radio Finalized by' when selected nothing. It should show one name as 'Radio Finalized by [name]' when selecting one value and it should show multiple names separated by a comma when selecting multiple values.

Below is my take on this -

Dynamic Title = 
VAR SelectedValues = VALUES(Sheet1[RadioFinalizedBy])
VAR SelectedCount = COUNTROWS(SelectedValues)
RETURN
    IF(
        SelectedCount = 0,
        "Radio Finalized by",
        IF(
            SelectedCount = 1,
            "Radio Finalized by " & SELECTEDVALUE(Sheet1[RadioFinalizedBy]),
            "Radio Finalized by " & CONCATENATEX(SelectedValues, Sheet1[RadioFinalizedBy], ", ", Sheet1[RadioFinalizedBy])
        )
    )

But with this measure when selecting nothing, all the values are displayed in the title.

Below is the screenshot. enter image description here

Solution

  • You can use ISFILTERED(...) to determine if it has been filtered.

    Dynamic Title = 
  var selectedValues =
    CONCATENATEX(
      DISTINCT(Sheet1[RadioFinalizedBy]),
      [RadioFinalizedBy],
      ", "
    )
  return
    IF(
      ISFILTERED(Sheet1[RadioFinalizedBy]),
      "Radio Finalized by " & selectedValues,
      "Radio Finalized by"
    )

    If you want to capture if all have been selected (which ISFILTERED will also return true) then you can have:

    Dynamic Title = 
  var selectedValues =
    CONCATENATEX(
      DISTINCT(Sheet1[RadioFinalizedBy]),
      [RadioFinalizedBy],
      ", "
    )
  var selectedCount = COUNTROWS( DISTINCT(Sheet1[RadioFinalizedBy]) )
  var allCount = 
    CALCULATE(
      COUNTROWS( DISTINCT(Sheet1[RadioFinalizedBy]) ),
      ALL(Sheet1)
    )
  return
    IF(
      selectedCount <> allCount,
      "Radio Finalized by " & selectedValues,
      "Radio Finalized by"
    )