Text slicer value that filters rows in table and produces text value

[lizlemon]

I have been all over trying to find the secret sauce to this.  I can create this in Excel, but I can't seem to figure it out in Power BI:

I have a list of names with 2 corresponding sites each.  I need a calculated column that will detect a selected Site value, give it a label, and apply it to the results.  

How I did it in Excel: 

I created a Table for Sites, which I used to define a name for a list of values for data validation.  I used this for the "Slicer" (dropdown cell) and for the 2 columns in the table defining SITEs.

My data table looks like this: 

SELECT SITE:

BBBB

NAME	SITE 1	SITE 2	Site Validation
Steven	AAAA	BBBB	HOME
Virginia	BBBB	BBBB	HOME
Allen	BBBB	DDDD	HOME
Corey	CCCC	AAAA	AWAY
Kari	DDDD	AAAA	AWAY
Miranda	DDDD	CCCC	AWAY
Jason	EEEE	QQQQ	AWAY

My formula for Site Validation (what I am trying to create in Power BI) is:  =IF(OR([@[SITE 1]]=SelectedSite,[@[SITE 2]]=SelectedSite),"HOME","AWAY")

I know you can't pass a slicer to a calculated column in Power BI, but I'm looking for an alternative.  It can't be impossible.  But if I try to use a parameter in Desktop, it does not give me a text option unless I am in the Query Editor.  Anyone have any ideas?

I have tried anyway using SELECTEDVALUE and it of course doesn't work, but it does if I put in a static text value instead.

[Irwan · Accepted solution]

hello @lizlemon 

please check if this accomodate your need.

for your case, i would like something like below

1. create a dim table for Site consisted of all Site. In this example, i summarize all combined value from SITE1 and SITE2.

if you have a dim table already or a list of Sites, then you can skip this step.

2. create a new measure with following DAX

Home Validation =
var _Site = SELECTEDVALUE('Site'[Table_SITE 1])
Return
IF(
    SELECTEDVALUE('Table'[SITE 1])=_Site||SELECTEDVALUE('Table'[SITE 2])=_Site,
    "Home",
    "Away"
)

3. plot in SITE dim table in slicer and the measure above in table visual

Hope this will help.

Thank you.

[lizlemon]

So, YES, this works as intended!  However it did present another challenge I am trying to work around.  I now have the HOME and AWAY values, but the table does not filter anymore with related table slicers.  

If you have any ideas for that, I am all ears, but I have to say that for my original problem, this worked like a charm!

[Irwan]

hello @lizlemon 

glad to be a help.

also do you mind describing more about the "table does not filter anymore with related table slicers.".

share some data would be great.

Thank you.

[lizlemon]

The issue is that if I want to filter the table view(s), the data does not filter with slicers or selecting anything that would normally filter a table visualization.

I have another table in Power BI that has all instances of when a person visits each site, but each person has 2 "Home" sites.  

The request is to filter the People that visit each site, and to label if they are a "Home" site person or an "Away" site person.  So I can filter by Site AAAA, and I will see everyone that has visited site AAAA, whether or not AAAA is their home site, but if they never visited AAAA, they would not appear in the table.

I did not provide this side data because I did not think about that part not working, but here is some sample data and expected results: 

Visit Data: 

Name	Site
Steven	BBBB
Virginia	CCCC
Allen	EEEE
Corey	AAAA
Kari	BBBB
Miranda	CCCC
Jason	EEEE
Steven	DDDD
Virginia	QQQQ
Allen	BBBB
Corey	BBBB
Kari	CCCC
Miranda	DDDD
Jason	QQQQ
Steven	AAAA
Virginia	AAAA
Allen	QQQQ
Corey	EEEE
Kari	DDDD
Miranda	QQQQ
Jason	FFFF
Steven	EEEE
Virginia	SSSS
Allen	CCCC
Corey	CCCC
Kari	TTTT
Miranda	BBBB
Jason	AAAA

Intended Results:  The table in Power BI would filter to this: 

SELECT SITE:

BBBB

NAME	SITE 1	SITE 2	Site Validation
Steven	AAAA	BBBB	HOME
Allen	BBBB	DDDD	HOME
Corey	CCCC	AAAA	AWAY
Kari	DDDD	AAAA	AWAY
Miranda	DDDD	CCCC	AWAY

If there is a modification for the measure to make this happen, I am all ears!  I'm sure that's got to be what it is if it's going to work.  🙂

[Irwan]

hello @lizlemon 

i might be misunderstood but i assumed your data has one column of Site instead of two columns as you have shown previously.

however, i am not sure how to decide which one become Site 1 and Site 2 since each Name has four site based on your sample data above. So i use all those four site, you can change based on your preferences.

But anyway, please check if this accomodate your need.

I tried to do your sample data in PQ and DAX, so you can choose any of them.

The Home Validation measure between PQ and DAX below looks different because of index ordering between PQ and DAX (again i dont know how to differentiate the Site).

the idea is to differentiate which Site 1 and Site 2, then plot them into Matrix visual since you have one column of Site.

Detail is in pbix attached.

Hope this will help.

Thank you.