Slicer with results from two different tables

[abde139]

Hello,

I am new to Power Bi, I am sorry if my question is too obvious.

I have the following problem: I have two tables, table1 and table2, and where each table has a unique "domain name" and "property name". Table 1 has all the existing records, as for table 2 it is generated daily with some missing records and I have to point out those missing records.

What I want to achieve, is whenever I select a domain name, I want a slicer that groups all properties while colouring missing properties. I was able to show the slicer of each table, but I could not group them in one.

I tried this dax but it does not seem to work (based on the results 1 or 0, I will create a rule to automatically colorate the cells in the slicer) :

MyMeasure =

VAR T2 = VALUES( table2[property] )

return

IF (SELECTEDVALUE('table1'[property])=T2 ,1,0)

Example:

Table1

domain	property	num
A	aa	10
A	bb	12
A	cc	20
B	ee	50
B	ff	70

Table2

domain	property	num
A	aa	10
A	bb	12
B	ff	70

Results:
when selecting for example domaine A, I should get a new slicer as (property cc is missing in table 2):

aa

bb

cc

Thank you in advance.

[tamerj1 · Accepted solution]

Hi @abde139 
I'm interested to know how are you planning to color individual values of a slicer. However, I can do that for a table visual for example. Please refer to attached sample file.

Format Measure = 
INT (
    ISEMPTY ( 
        INTERSECT ( 
            VALUES ( Table1[property] ),
            VALUES ( Table2[property] )
        )
    )
)

[abde139]

Thank you for the brilliant answer ! as for coloring individual values of a slicer, I was planing to apply the same rule you did on the table's cell ( as shown in the image ). However, It does not work. I suppose from your answer that it is not possible ?

[tamerj1]

@abde139 
This applies to whole background of the slicer. I'm not a visual superhero but as far as I know that until this moment, formatting individual slicer values either, text, boarder or background is not yet possible.

[johnt75]

Create a new table with just the domains in, like

Domains =
DISTINCT (
    UNION ( DISTINCT ( 'Table1'[domain] ), DISTINCT ( 'Table2'[domain] ) )
)

and link this table in one-to-many relationships to both table 1 and table 2. You can use the column from the domain table in your slicers and visuals.

You can then tweak your measure to

MyMeasure =
VAR T2 =
    VALUES ( table2[property] )
RETURN
    IF ( SELECTEDVALUE ( 'table1'[property] ) IN T2, 1, 0 )

[abde139]

Thank you for your kind reply.
I followed every step, but I still can not distinguish by color the missing properties from table. (I used the background color on the final slicer as shown in the image)

[johnt75]

It wouldn't work on the title as there is no selected property. You would need to do something on a row where there is only 1 property.