cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBNikr Regular Visitor
Regular Visitor

Diff of selected value from slicer and selected value in table

 

I am struggling with the below example - all help is appreciated! (Please note that I am using direct query mode).

 

Example:

I have a slicer with the possibility to select Red, Yellow, Blue and Green. Interactions have been disabled for a table where the user can also select a value of Red, Yellow, Blue and Green (each with its own rank based on a percentage value).

 

I would like the user to be able to select the color Blue in the slicer and Red in the table. I need a measure to retrieve the number of placements between Blue and Red, and the difference in percentage. 

 

 

Color Red, Rank 2, Percentage 86%
Color Blue, Rank 4, Percentage 54%

Two separate measure would have the value of 2 and 32%.

 

 

At this point I can retrieve the selected options used enable/disable interaction, but I need the diff values. I cannot seem to figure out how I can connect these values in a measure.

6 REPLIES 6
Super User
Super User

Re: Diff of selected value from slicer and selected value in table

Hey,

 

unfortunately I'm not able to fully understand your requirement. for this reason I ask you to create a pbix file that contains sample data, upload the file to onedrive or dropbox and share the link. If you are using an Excel file to create the sample data please upload the Excel file as well.

 

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
PBNikr Regular Visitor
Regular Visitor

Re: Diff of selected value from slicer and selected value in table

@TomMartens 

 

Here is a picture from PBI that shows what I am trying to do (using direct query mode):

image.png

 

v-lid-msft Super Contributor
Super Contributor

Re: Diff of selected value from slicer and selected value in table

Hi @PBNikr ,

 

Though you are in DirectQuery Mode, so we cannot create calculated table, but we can connect to the same data source again using DirectQuery Mode, name the new table as Diff-copy, use the color column in Diff-copy table as slicer, and the origin table as the table, then we can use the following measure to meet your requirement:

 

RankDiff = ABS(MAX('Diff-copy'[Rank]) - MAX('Diff'[Rank]))

 

PercentageDiff = FORMAT(ABS(MAX('Diff-copy'[Percentage]) - MAX('Diff'[Percentage]))/100,"Percent")

6.PNG7.PNG8.PNG9.PNG

 

Sorry for that I cannot provide sample pbix file because it use DirectQuery Mode.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PBNikr Regular Visitor
Regular Visitor

Re: Diff of selected value from slicer and selected value in table

@v-lid-msft Thanks for your response. I don't think this will be possible since I am connected to a tabular model. But maybe there is a workaround for this as well?

v-lid-msft Super Contributor
Super Contributor

Re: Diff of selected value from slicer and selected value in table

Hi @PBNikr ,

 

If you are using LiveConnection Mode, you can just create a Calculated table in AS to get the copy of table, You can get more information from this document: https://docs.microsoft.com/en-us/analysis-services/tabular-models/create-a-calculated-table-ssas-tab...

 

After you get the copy of origin table, we could create measure in AS to meet your requirement.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft Super Contributor
Super Contributor

Re: Diff of selected value from slicer and selected value in table

Hi @PBNikr ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 287 members 3,236 guests
Please welcome our newest community members: