topic Re: Slicer with results from two different tables in DAX Commands and Tips

Slicer with results from two different tables

abde139 — Wed, 03 May 2023 10:12:03 GMT

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):

Thank you in advance.

Re: Slicer with results from two different tables

johnt75 — Wed, 03 May 2023 10:58:11 GMT

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 )

Re: Slicer with results from two different tables

tamerj1 — Wed, 03 May 2023 12:12:30 GMT

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] ) ) ) )

Re: Slicer with results from two different tables

abde139 — Wed, 03 May 2023 12:13:15 GMT

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)

Re: Slicer with results from two different tables

abde139 — Wed, 03 May 2023 12:36:54 GMT

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 ?

Re: Slicer with results from two different tables

johnt75 — Wed, 03 May 2023 12:41:41 GMT

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.

Re: Slicer with results from two different tables

tamerj1 — Wed, 03 May 2023 12:42:17 GMT

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.