Two Columns Data to bring into One Slicer

[SantoshKumar]

Hi All,

Due to Data sensitivity, i am representing the values as below.

Requirement: 
I have two columns as below

Column1	Column2
Apple	Blue
Orange	Black
Apple	Green
Apple	Green
Apple	Green
Orange	Green
Apple	Green
Others	Black
Orange	Blue
Others	Blue
Apple	Black
Orange	Blue
Others	Green
Others	Blue
Orange	Black
Others	Green
Orange	Green
Others	Black
Apple	Blue
Apple	Black

Here i am concerned with only three data point: two values from Column1 and one from Column2.

Apple	8
Orange	6
Green	8

I have to bring up a Slicer which should show values of only "Apple,Orange and Green" and it need to project the same count as mentioned above.
But when i try to apply condtions involing two columns then i am getting unique values as per selection. for example, Apple and Orange are showing as expected but when i select Green then it shows as 2 (as other 6 values are part of Apple & Orange).

Regards

Santosh Kumar 

[Anonymous · Accepted solution]

Hi @SantoshKumar ,

Create a slicer table like below:

Then create a measure:

Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Column1]=SELECTEDVALUE(Slicer[value])||'Table'[Column2]=SELECTEDVALUE(Slicer[value])))

[amitchandak]

@SantoshKumar , if you want that king of display, then you need to unpivot the table

https://radacad.com/pivot-and-unpivot-with-power-bi

Or create a table

distinct(union(distinct(Table[Column1]) ,Table[Column2]) ))

join with both the columns, one join will active and one will inactive ,

then use userelationship and use that measure to create

dim = count(Table1[Colum1]) _ calculate(count(Table1[column1], userelationship(dim[column1], table[column2]))

assume join with column two in inactive