Forum Discussion
Helper IIHow to get common data from selected value in slicer
Hi Sha ,
First of all, we can create a separate table to show the data, then we can use a measure in visual filter to filter it:
TableToCompare = 'Table'HasSameClient = IF ( AND ( SELECTEDVALUE ( 'TableToCompare'[Client] ) IN DISTINCT ( 'Table'[Client] ), NOT SELECTEDVALUE ( TableToCompare[Bus unit] ) IN FILTERS ( 'Table'[Bus unit] ) ), 1, -1 )If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.
Best regards,Hi Sha ,
We can use the variable to optimise the code, if you have any other questions, please kindly ask here and we will try to resolve it.
# of Bus Units = VAR SelectedBus = SELECTEDVALUE ( 'Table'[Bus unit] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'TableToCompare'[Bus unit] ), 'TableToCompare'[Bus unit] <> SelectedBus )
Best regards,
Community SupportHi Sha ,
First of all, we can create a separate table to show the data, then we can use a measure in visual filter to filter it:
TableToCompare = 'Table'
HasSameClient =
IF (
AND (
SELECTEDVALUE ( 'TableToCompare'[Client] ) IN DISTINCT ( 'Table'[Client] ),
NOT SELECTEDVALUE ( TableToCompare[Bus unit] ) IN FILTERS ( 'Table'[Bus unit] )
),
1,
-1
)
If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.
Best regards,
Helper III thought that was working. But mine is doing something yours is not. Mine is showing the selected value in the totals. Your slicer is from Table? Top visual is where HasSameCliet = -1 and Bottom visual is where = 1? Should I have a relationship in the model for the new table?
The other thing that I have different, is I a have a date in Table and related to a date table. Perhaps I need to relate the new table to the date table or change the measure?
Thank you for you help.
- v-lid-msft
Hi Sha ,
Sorry for that we forgot to put the sample pbix file in our previous post. The above visual comes from the origin table and the below is come from the copied table, we only applied the visual filter in the below visual. We did not set relationship between the two tables.
Best regards,- Sha
Thank you for your reply and help.
Top visual will include all Sales whether or not related to same client. I need to show only those related.
Bottom visual is great for the detail.
I'm now looking to roll that up to a summary, but when I change Bus Unit to distinct count and Sales to Sum, they now include the Bus Unit selected and I don't want that.
- Sha
I have over 400,000 rows and the below measure is taking over 5 minutes to perform but I think I have solved for returning counts the way I would like to see them. Any help to optimize would be appreciated.
# of Clients = CALCULATE(DISTINCTCOUNT('Table'[Client]),filter(TableToCompare,TableToCompare[Bus unit]<> SELECTEDVALUE(TableToCompare[Bus unit])))
- Sha
Wrote previous measure incorrectly...
corrected...
# of Bus Units = CALCULATE(DISTINCTCOUNT('TableToCompare'[Bus unit]),filter(TableToCompare,TableToCompare[Bus unit]<> SELECTEDVALUE('Table'[Bus unit])))