Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello,
I have a little problem, where I cannot seem to find the answer to.
I have one table with all the Information in it, and in that Table is a Column, that has TRUE or FALSE values in it. Further there is an ID Column, that is used in a Slicer.
Now to the Problem. I want to show in a Table all the vaules that are marked TRUE plus the values, that are selected in an ID slicer.
So I expect the following Result when ID 2 & 3 are selected in the Slicer:
ID | True/False Colum | Expected Result |
1 | True | True |
2 | True | True |
3 | False | True |
4 | False | False |
I tried it with some versions of the following code but I cannot get it to work:
ShowRow =
VAR Always = SELECTEDVALUE('OfflineExcel'[AV_Marked]) = TRUE()
VAR IsSelected =
CONTAINS(
VALUES('OfflineExcel'[Av_Text_Combined]),
'OfflineExcel'[Av_Text_Combined],
SELECTEDVALUE('OfflineExcel'[Av_Text_Combined])
)
RETURN Always || IsSelected
And there can be syntax errors in that Code, because of frustration I deleted the DAX 😅 and I recreated it here out of memory.
Thanks and best regards
Lars
Solved! Go to Solution.
You will need a separate, disconnected table to use in the slicer. You can create one with something like
Table for Slicer = DISTINCT( 'Table'[ID] )
Do not connect the new table to the main table.
Now you can create a measure like
Show Row =
IF (
SELECTEDVALUE ( 'OfflineExcel'[AV_Marked] )
|| SELECTEDVALUE ( 'OfflineExcel'[ID] ) IN VALUES ( 'Table for slicer'[ID] ),
1
)
Add that as a filter to your table or matrix visual, set to show only when the value is 1.
That's why you need the disconnected table for the slicer. Making any selection in the slicer should not affect the table at all, unless you place a measure filter on the table.
To be clear, the slicer needs to use the disconnected table, the table visual should use values from the main table.
You will need a separate, disconnected table to use in the slicer. You can create one with something like
Table for Slicer = DISTINCT( 'Table'[ID] )
Do not connect the new table to the main table.
Now you can create a measure like
Show Row =
IF (
SELECTEDVALUE ( 'OfflineExcel'[AV_Marked] )
|| SELECTEDVALUE ( 'OfflineExcel'[ID] ) IN VALUES ( 'Table for slicer'[ID] ),
1
)
Add that as a filter to your table or matrix visual, set to show only when the value is 1.
Hello John,
Thanks for your response as well. However, this also does not work. The Problem now is, that when the table and the Slicer interact with each other, and in the slicer a row is selected, only that row is shown in the Table. It should show that value and all the others marked true.
When I turn of the interaction, the all the values are shown, and the slicer does not work.
That's why you need the disconnected table for the slicer. Making any selection in the slicer should not affect the table at all, unless you place a measure filter on the table.
To be clear, the slicer needs to use the disconnected table, the table visual should use values from the main table.
Thank you, John, for the Answer, that did Work 👍
@LATH , Try using
DAX
ShowRow =
VAR Always = 'OfflineExcel'[True/False Column] = TRUE()
VAR IsSelected =
COUNTROWS(
FILTER(
ALLSELECTED('OfflineExcel'),
'OfflineExcel'[ID] = EARLIER('OfflineExcel'[ID])
)
) > 0
RETURN Always || IsSelected
Proud to be a Super User! |
|
Hello Bhanu,
thanks for the Quick rsponse. However, if it is a measure, it does not work, since earlier is not a measure object. And when I tried it in a new Row I get the full amount as true which should which should not be the case.
Thanks
Lars
User | Count |
---|---|
17 | |
14 | |
14 | |
13 | |
12 |
User | Count |
---|---|
19 | |
15 | |
15 | |
11 | |
10 |