DAX Measure Intersect with Slicers

[DavidB2123]

How do I do the equivalent of the following T-SQL code in a DAX measure:

 Also, I don't want to hard code values. I want Product and InsuranceCompany fields to be slicers.

select distinct(Table1.Client)

from Table1

where Table1.Product ='AUTO' AND Table1.InsuranceCompany = 'AMCO'

INTERSECT

select distinct(Table1.Client)

from Table1

where Table1.Product ='FIRE' AND Table1.InsuranceCompany   = 'Allied P & C Ins. Company'

Thanks in advance.

[Greg_Deckler]

This should be doable. Can you post some sample data in a format that can be copy and pasted easily please? It's a lot of work to manually create the data.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[DavidB2123]

I apologize for lack of data. Here is a sample table:

Product	InsuranceCompany	Client
AUTO	AMCO	David Brown
FIRE	Allied P & C Ins. Company	David Brown
AUTO	AMCO	James Smith
FIRE	Allied P & C Ins. Company	James Smith
AUTO	AMCO	Jeff Wilson
FIRE	Allied P & C Ins. Company	Jeff Wilson
AUTO	AMCO	Linda Mcdonald
FIRE	Allied P & C Ins. Company	Linda Mcdonald
AUTO	AMCO	Sarah Engler
FIRE	Allied P & C Ins. Company	Sarah Engler
HOME	Chubb	John Johnson
BOAT	Transamerica	Bill Jones
PETS	Allied Group	Jack Johnson

[DavidB2123]

Thank you for the quick reply. I'll let you know if this works for my model.

[Greg_Deckler]

Technically, you do not need the relationships for the measure to work but you will need two sources for Product and Insurance Company for your slicers because you need them to be indepenent of one another.

[DavidB2123]

Thanks much Greg that helped a lot. Once last thing, the resultset is coming back in one cell of a table,

(Joe Johnson, Jack Jones, Joe Smith) all together. Is there a way to split the results into individual rows?

Thanks again for your help!

[Greg_Deckler · Accepted solution]

Yeah, that's an issue. You could create a New Table and just return the unconcatenated result but this would not be dynamic. See my idea about this here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33890335-table-visualization-tied...

Vote for it!!

[Greg_Deckler]

OK, if you have this:

Then you can do this:

Clients = 
VAR tmpTable1 = FILTER(ALL('#Products'),'#Products'[Product]=MAX('#ProductsProduct'[Product])&&'#Products'[InsuranceCompany]=MAX('#ProductsInsurance'[InsuranceCompany]))
VAR tmpTable2 = FILTER(ALL('#Products'),'#Products'[Product]=MAX('#ProductsProduct (2)'[Product])&&'#Products'[InsuranceCompany]=MAX('#ProductsInsurance (2)'[InsuranceCompany]))
VAR tmpTable1a = DISTINCT(SELECTCOLUMNS(tmpTable1,"Client",[Client]))
VAR tmpTable2a = DISTINCT(SELECTCOLUMNS(tmpTable2,"Client",[Client]))
VAR retTable = INTERSECT(tmpTable1a,tmpTable2a)
RETURN CONCATENATEX(DISTINCT(retTable),[Client],",")

[DavidB2123]

How do I do the equivalent of the following SQL code in a DAX measure:

select distinct(Table1.Client)

from Table1

where Table1.Product ='AUTO' AND Table1.InsuranceCompany = 'AMCO'

INTERSECT

select distinct(Table1.Client)

from Table1

where Table1.Product ='FIRE' AND Table1.InsuranceCompany   = 'Allied P & C Ins. Company'

Another piece is that I don't want to hard code values. I want Product and InsuranceCompany fields to be slicers.

Thanks in advance.