Bring Selected Value from Slicer into Table Visual in Power BI

[sf94]

I am working in Power BI and have imported the general ledger (GL) of a company. The GL is structured with columns: JournalID, Account, Amount, and DocLine. Each JournalID has multiple rows due to double-entry bookkeeping. I have created a table with 6 rows as an example:

JournalID	DocLine	Account	Amount
1	1	101	500
1	2	200	-500
2	1	101	300
2	2	300	-300
3	1	102	1000
3	2	300	-1000

In Power BI, I have a slicer and a table visual mentioned above. The slicer allows filtering by accounts (filter as example for account 101). What I need to achieve is the following: the table should remain with exactly 6 rows (i.e., no filtering by account should occur). However, a new column should be added that has the value 1 for all JournalID's, that have the selected account in one of their rows and 0 for all JournalID's that do not have the selected account from the slicer. It is crucial that the visualisation with the table still has 6 rows. Result should look like this:

JournalID	DocLine	Account	Amount	Measure
1	1	101	500	1
1	2	200	-500	1
2	1	101	300	1
2	2	300	-300	1
3	1	102	1000	0
3	2	300	-1000	0

The solution should be dynamic, meaning, when I change the account from the slicer, the results should change too. I think I can achieve it by creating a measure and stop the interaction between slicer and table visual.

Thanks for your help!
sf94

[johnt75 · Accepted solution]

Create a new table like

Account Slicer = DISTINCT( 'Table'[Account] )

and use this in your slicer. Do not connect it to the main table.

You can then create a new measure like

Account matches slicer =
IF (
    SELECTEDVALUE ( 'Table'[Account] ) = SELECTEDVALUE ( 'Account Slicer'[Account] ),
    1,
    0
)

Add this to a table visual using the account column from the main table, not the one from the slicer.