Trying to create OR slicer between two columns (Team and Shared Team)

[DavidGM]

            NAME       Team            Shared Team        Hours

1	Alex	Integration	Main Integration	2
2	Alex		Main Integration	3
3	Alex	Integration		2
4	Jamie	Integration	Main Integration	2
5	Joseph	Security	Security Team	1
6	Smith	Development	Custom Develop	2
7	Smith		Custom Develop	2
8	Dean	Testing	Testing Team	4
9	Dean		Testing Team	1
10	William	Database	Testing Team	3

I like to see hours worked by Team Or shared team field so I can see how much

work individual assignee is working. So I can filter on team or shared team from

the single list (Team + shared team becomes one column). OR Slicer

[FreemanZ]

Hi @DavidGM 

You would need the two slicers from two different tables, like below:

1) The model:

(Hint: the two dimensional table can be duplicated from the Data table in Power Query)

2) The result:

3) The code for the measure:

TotalHours =

VAR _table1 =

CROSSJOIN (

    ALL(Team[Team]), VALUES('Shared Team'[Shared Team])

)

VAR _table2 =

CROSSJOIN (

   VALUES(Team[Team]),ALL('Shared Team'[Shared Team])

)

VAR _table3 =

UNION (_table1, _table2)

RETURN

    CALCULATE (

    SUM(Data[Hours]),

    _table3

)

[DavidGM]

Thanks, I like to understand that model a bit better. As Team (coming from Main Table) and Shared Team (coming from Effectiveness table ), Hours (comig from Main Table) and these tables already have relationship. So you suggesting I should break the relationship between them? As I tried that measure it still gives me AND values. Thankyou!

[FreemanZ]

Indeed it hides some complexity. The point is the two fields you put to slicer need to be independent from each other, so comes the need to break the relationship of the two sliced fields. Otherwise when you make a selection at one, the other changes accordingly. So you need at least three tables in your model: one record table(for the Hours value) and two dimensional table (for the slicers) .

[Mikelytics]

Hi @DavidGM 

my start table

if you have emtoy fields which are not null do the following

Trim

Replace "" with null

in between result:

now fill down

Now add a column

if Text.Trim([Shared Team]) = "" or [Shared Team] = null then [Team] else [Shared Team]

Now delete the old Shared Team column

and rename the new one from "SHared Team New" to "Shared Team"

now you have cleansed both columns

you can also combine the two columns in one column

[Team] & "-" & [Shared Team]

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[DavidGM]

Thanks, but now if I like to use the filter Integration, will that show me data from Combined column ?

[Mikelytics]

@DavidGM 

I am not sure what you want to do but you could take a table visual and put the "combined" field in there. Then you take two slicers. IN one slicer you put the "Team" column and in the other slicer you put the "Shared Team" slicer.

table visual for "Combined" field

two slicer for "Team" and "Shared Team"

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.