Filtering two columns

[Anonymous]

I have data that has two text columns and a value field.  For example:

Col1      Col2      Val

A            B           10

A            C           5

B            A           20

B            C           15

C            A           10

C            B            15

What I'd like to create is a slicer that enables me to select A, B or C and then be able to filter that table to any row that contains the selected letter in either Col1 or Col2.

My problem lies in the Col1 and Col2 fields being text fields.  Because of this, I can't create a dummy calculation of "Selected Letter", because MAX, MIN etc won't work with text fields.  

To get around this I thought I could create an Identity column to use IDs instead of letters, I'm struggling to create an Identity column to normalise the Col1 and Col2 values.

I've also tried splitting the data into two tables (Col1 & Val, Col2 & Val), which I can filter, but then recombining them into a new dataset for charting is giving me the whole set instead of just the filtered set.

Anyone got any thoughts or ideas?

Thanks

Simon

[MattAllington]

Create a disconnected table containing A, B, C

write a measure Selected letter = Values(table[column])

write another measure 

display =Calculate(sum(data[value]),filter (data, data[col1]=[selected letter]),

      Filter(Data,data[col2]=[selected letter])

)

i do t know what will happen if you select 2 letters - it will probably break. You may need to wrap the whole thing inside an if(hasonevalue()) construct. 

[Anonymous]

Thanks @MattAllington - this helps a bit, but I want to return all the rows, not add up the values.  How would you approach that?

[MattAllington]

One way would be to change the measure to return true for the match or BLANK for no match. That will return all he rows, but with an unfortunate "true" next to each. A cleaner approach possibly is to use a many to many pattern.

Create a calc column in your data table that returns a concatenation of the A,B,C columns (2 chars). Then build a new data table with 2 columns that contains all the combinations of both Colums (I will call it data2). Eg

A. AA

A.  AB

B.  AB

etc

Then build a new lookup table with just A, B, C etc (you already have this but it is disconnected.  I will call it lookup1)  Then build a new lookup table with all the unique values of the concatenation column. I will call it lookup2.  

join the new calc column from the original data table to lookup 2, bidirectional 

join data2 to lookup2, bidirectional 

join data2 to the A,B,C table, single direction.  

I'm thinking it should work.