Data in table is not being filtered interactively based on a custom column in a chart

[Ronnie7]

Hi All,

I have created a custom column that is looking at another column and chooses only 2 specific values (Yes, Pending), instead of the 3 available values,  please see below:

Column   = CALCULATE (
    COUNTA( Projects[PMAssigned]),
           ALL ( Projects[PMAssigned]),
        Projects[PMAssigned] = "Yes"
        ||Projects[PMAssigned] = "Pending" )

The code seems to work well. The problem is when i click this column in a clustered column chart. Below the chart, on the same page, I have a table. In the  table I have data containing some fields along with the PMAssigned field (the "vanilla" field from which im filtering data out in the custom field) , the data in the table is not being filtered interactively upon clicking the custom column in the chart. 

In order to have the data in the table  filtered out based on a column/value in the chart, I have to use the "Vanilla" column.

I want the data in the table to be filtered out interactively based on this custom column.

Is there anyway around it?

Thanks!

[Sean]

@Ronnie7

Try this as a MEASURE!

MEASURE =
CALCULATE (
    COUNTA ( Projects[PMAssigned] ),
    FILTER (
        Projects,
        Projects[PMAssigned] = "Yes"
            || Projects[PMAssigned] = "Pending"
    )
)

This should do it!

[Ronnie7]

@SeanI have updated the formula, the problem is that now it shows the amount of total rows in the table reghardless on the

filter Yes/Pending. The data in the table below the chart is still not changing according to the custom field.

See schreenshot below (it shows all three values), any other suggestions?

[Sean]

Did you add the new MEASURE to the table?

[Ronnie7]

@Seanthanks for your help on this! here are couple of comments:

1. I did create a new measure and not a new column

2. The Total is correct ( i was wrong im first reply saying it wasn't).

I now added the measure to the table and it looks good, however, I'm wondering wether it's possible to filter the results of the 

"vanilla" field in the table based on the measure?  so for instance, here's a screenshot of the chart with the original field in it:

If i choose the value "no" in the chart, the table will show the data accordingly. Can this be done when choosing the new Measure in the chart? so the values for the original field in the table will show only "yes" or "pending"...this is per a task i was given. This chart will appear in the dashboard and will have a drill down to the underlying data, wanted to see if it could be filtered right away to yes/pending... 

I get the feeling that it is not possible, but i'd like to double check.

Thanks Again!

[Sean]

If you are using the Measure I provided above only the "YES" and "Pending" columns should be plotted on your chart!!!

There should be no "NO" column!

In your table if you have other Measures that include the "No" as their answer then yes the "No" will show in the table.

But again in the chart the "NO" column should not be there if you are using my measure.

In fact if you use a Slicer with this column (the one from the Measure) Projects[PMAssigned] (which you say has 3 values only)

If you select NO from this slicer - the chart should be blank! because my measure should give you blank as answer

And with the slicer deselected...

Hope this helps! Good Luck

[Ronnie7]

@Seanthanks again for your help.

The chart is ok  and it shows only pending/yes values  when using the measure with your formula, no issues until here.

The original field [PMassingned] has 3 values : Yes/no/pending. The latest screenshot in my last post was for that field and this is why there was a "no" value in the chart. I wanted to demonstrate what im trying to accomplish. 

I haven't done the best job of explaining myself. I wonder whether it's possible to show only Yes/Pending values in the table for the [PMassingned] field when selecting the measure in the chart so in the table, i will see only "Yes" or "Pending" values  for the [PMassingned] field. 

I know that i can easily use a slicer for that purpose, but i'm curious to know if it is possible to accomplish that using the measure.

I hope i am more clear now on what i have tried to accomplish, sometime it's hard to explain in writing.

[Sean · Accepted solution]

So you want the chart to show a count of each of the values including the NO but the table only the Yes and Pending Numbers?

To quickly test this just drag Projects[PMAssigned] to the Value area of the chart instead of the Measure

You'll get a generic Count of... AND use my Measure only in the table!

Again however if you have other Measures that don't have the same FILTER argument and therefore include the NO as answer

then the NO will keep showing in the Table - hope this makes sense

to test it leave only my Measure in the Table and the Projects[PMAssigned] field

Good Luck!