Filter for multiple values in one cell

Hello,

i have a data model in which the main table sometimes includes columns in which several values are included in one cell to minimize the numer of rows and columns needed.

e.g.

Risks	IT-Protection Goals
G0.1; G0.2; G0.45; G036	C; I; A
G0.8; G0.1; G0.2;	C; A
...	I

I know want PowerBI to detect e.g. G0.2 in one of the cells and not view the content of the cell as one value. I know that I could split the data into several columns but I specifially want to avoid that and put all the individual values into one cell.

Help of any kind would be highly appreciated.

Best

Justus

tamerj1

3 years ago

KubenM
Sorry for the late response. I was trapped in a couple of meetings. I hope the following is what you're looking for.

Count of BE Key = 
CALCULATE ( 
    COUNTROWS ( VALUES ( 'Table'[BE Key] ) ),
    FILTER ( 
        'Table',
        VAR SelectedValues = VALUES ( FilterTable[Item Value] )
        VAR String = 'Table'[Fixed Version]
        VAR Items = SUBSTITUTE ( String, " , ", "|" )
        VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
        VAR T1 = GENERATESERIES ( 1, Length, 1 )
        VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
        RETURN
            COUNTROWS ( INTERSECT ( T2, SelectedValues ) ) 
    )
)

Community 510.pbix30 KB

39 Replies

KubenM
Advocate II
3 years ago
Hello
I have a similar challenge. I am trying to stay away from complex DAX.
I have a filter setup for Increment that uses a Fact Table that has the following individual values defined:
Y23PI1
Y23PI2
Y23PI3
Y23PI4

My dimension table reflects that some rows have mulitple Increments in the same cell for example: Y23PI1, Y23PI2 or Y23PI2, Y23PI3, Y24PI4
As my filter only references single Increments, when I filter on Y23PI1 or Y23PI2, Power Bi returns zero as it can't find the single Increments.
Please assist if possible.
Kind Regards
- tamerj1
  Community Champion
  3 years ago
  KubenM
  I don't beleive there is any solution other than DAX. However it is not as complex as you might think. Please refer to attached sample file with the proposed solution
  
  Filter Measure = VAR SelectedValues = VALUES ( FilterTable[Item Value] ) VAR String = SELECTEDVALUE ( 'Table'[Item Values] ) VAR Items = SUBSTITUTE ( String, ", ", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = GENERATESERIES ( 1, Length, 1 ) VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) ) RETURN COUNTROWS ( INTERSECT ( T2, SelectedValues ) )
  Community 510.pbix26 KB
  - KubenM
    Advocate II
    3 years ago
    Thank you so much for your solution to my problem. This is absolutely brilliant and is exactly what i required. Much appreciated. WoW.
tamerj1
Community Champion
4 years ago
Anonymous
The idea is applicable and can be achieved. Please provide an example of what exactly are you trying to achieve?
- Anonymous
  Not applicable
  4 years ago
  Thx for your resposne. I'm e.g. trying to display the top5 risks. Another example would be to give a more detailed view on the risks by connecting another table where each risk is listed with more dteailed information. In general I just want to work with the individual values, use them in visuals, ect.
tamerj1
Community Champion
4 years ago
Hi Anonymous
Sorry for the late reply. Here is a sample file for your reference https://we.tl/t-ROzVPTGfiW
The first thing to to is to extract the unique values of each column:
Then the rest would be simple. Here are some examples of what can be calculated.
Please provide more realistic sample of data and advise exactly what is required in order to support you further. Thank you
Anonymous
Not applicable
4 years ago
Hi Anonymous ，

Has this question been solved? If tamerj1's answer is helpful, please consider marking his answer as a solution, which will help more people find the answer faster. Thanks in advance!😊

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data