Slicer does not filter a calculated measure in PBI table

[fvdyoshida]

Hi everyone, I am getting craaazy with this one. The slicer that I used did not affect a calculated measure in a table, it only works on a “card visual”. If I select the slicer for an user, in the card I can see his metrics calculated properly, but in the table I see the calculation for all users.

this is the Metric Table

Metric_ID	Value
Metric1	CalculatedMetric_DAX
Metric2	CalculatedMetric_DAX

where:

CalculatedMetric_DAX = if(Metric_ID=”Metric1”,’#metric1_DAX measure’,if(Metric_ID=”Metric2”,’#metric2_dAX measure’,0)

#metric1_DAX = 1 - ((10 - 'satisfaction score)/10)

the idea is to check against the Metric_ID and get the calculations from other tables.

#metric2_DAX = 1 - (('resolution_time' - 3)/'resolution_time)

all metrics are in percentage.

The slicer filter “User names” from other table.

In the report table I see

shows the total calculated value not the value calculated by user.

Metric	Value
Metric1	9
Metric2	6

but those values are the sum for all users, in the Card I see the Metric1= 4.8 & Metric2= 6.1

any thoughts?  any help would be highly appreciated.

thank you in advance

Fabio

[fvdyoshida]

Hi Tamerj1,

here you can find a pbix copy  https://1drv.ms/u/s!Am4oytAhDwtXah2WbDEJhqgYMFo?e=Xf8rOm

Thank you

Fabio

[tamerj1 · Accepted solution]

Hi @fvdyoshida 
Here is the file with the solution https://www.dropbox.com/t/mPaCeHRKW2Lkobju
Columns cannt read the filters. It has to be a measure. However I've looked into your file and noticed that the issue originated from not using the proper data set and data model. The vendors data has to be unpivoted and appended all in one table. You can see the power query stepts to do that in the file. Don't worry it is simple.
The final Vendors table looks like this

Then set simple one-many relationships like this

The rest is piece of cake. Simple measure:

Metric = AVERAGE ( Vendors[Value] )

% Metric = 1 - ( ( 10 - [Metric] ) /10 )

You can use slicers to filter any data and it will be automaticall updated in the visual.
Please let me know if you need any further help.

[fvdyoshida]

Hi Tamerj1,

thank you for the help, I am now replicating to my real environment  ( some vendors have more than 20 diffent metrics) but I got another issue.

on % Metric calculation I have 3 different ones:

if Metrics is a score (0-10) : % Metric = 1 -((10-[Metric])/10)
if Metric is a # events :   % Metric = 1 - ((100 - [Metric])/10)
if Metrics is time :    % Metric = 1 - ((AVG(TIME) - 5)/AVG(TIME)

when I tried to created this measure on the append table I was not able to see the [Metric table] field to apply those % Metrics based on if is Metric 1, 2 or 3.

any ideas?

thank you a lot my friend

[tamerj1]

@fvdyoshida 
So you have a metric category column? Can please share a sample or a screenshot?

[fvdyoshida]

I do not have have category column, i user the column Metric Table , so from there I was thinking about filter by Metric 1, 2 or 3 and assign diffent % Metrics measures. 

because % calculation will vary depending on the type, (if the target is a date, a number or a scoire).

is that possible?

https://1drv.ms/u/s!Am4oytAhDwtXah2WbDEJhqgYMFo?e=bw55pZ  

[tamerj1]

Do you mean to say that Metric 1 is for "Score", Metric 2 is for "# of Events" and Metric 3 for "Time"?

[tamerj1]

@fvdyoshida 

Can you please share some screen shots. A sample file would be a great start.