powerbicontainskeyword

How to use slicer in power bi as a keyword search in dataset which contains the keywords?

Objective: To create 2 visuals in power bi to allow user when they click the keyword in slicer, the corresponding table will only shows the line item that contains the keyword selected

DATASET:

Activity Amount
Company dinner at hotel ABCD $100
Lunch at the hills $70
Flower wraith 20/09/23 $20
Tokens for retirement $20
Dinner with MR ABCD $60
Staff XXX claims for dinner 24/06/23 $50

-

*Thousands of line items

POWER BI:

VISUAL 1:

SLICER

Keywords
Dinner
Lunch
Family
Gifts
Tokens
Flower

VISUAL 2:

Activity Amount
Company dinner at hotel ABCD $100
Lunch at the hills $70
Flower wraith 20/09/23 $20
Tokens for retirement $20
Dinner with MR ABCD $60
Staff XXX claims for dinner 24/06/23 $50

*The user click/select one of the keyword for example in this case "Dinner" the table only show the line item

Activity Amount
Company dinner at hotel ABCD $100
Dinner with MR ABCD $60
Staff XXX claims for dinner 24/06/23 $50
Total $210

I have tried using DAX contains strings, adjusting the filter well but all seems returning error or nil result

Solution

  • Create a Measure similar to:

    Keyword count = 
  IF(
    ISFILTERED(Keywords[Keywords]),
    SUMX(
      DISTINCT('Keywords'[Keywords]),
      COUNTROWS(FILTER('Activity', CONTAINSSTRING('Activity'[Activity], [Keywords])))
    ),
    COUNTROWS(Activity)
  )

    Add this new measure to your visual as a Visual Filter, and set it to is not blank, in the Filter pane.

    Suggest your keyword list is non-plural (gift instead of gifts as an example).