Diff of selected value from slicer and selected value in table

[PBNikr]

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.

[TomMartens]

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

[PBNikr]

@TomMartens 

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

[v-lid-msft]

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

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]

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

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.

[v-lid-msft]

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.