Display Table following filter based on slicer from other table

[karimm]

Hi All

I need to display contents of table after filtering it based on slicer selection from other table.

TableA

'Key'     'PI'

TableB

'PI'   'Application'    'Total Ready Days'  'Total Pending Days'

These 2 tables don't have a direct relation.

Thanks a million in advance

I have a slicer for selecting 'Key'.

On same page, I want to display the contents of Table B in a stacked column chart.

'Application' will be in the x axis and then it will stack 'Total Raedy Days' and 'Total Pending Days' in a column

What I need is that the chart will display the content of Table B after it's been filtered on the PI value selected in the slicer coming from Table A.

I tried writing a measure that I will use to filter the chart:

Filter Table = 
var _selectedPI = SELECTEDVALUE(TableA[PI])
var _rowPI = FIRSTNONBLANK(TableB[PI],TRUE)
return
IF(_rowPI= _selectedPI , 1, 0)

I'm not getting desired results. I believe it's because the _rowPI is not giving the correct value.

Can you please advise what could be done here?

[karimm]

Just providing a further update to this post...

Due to not getting the required results from the filter measures, I ended up using CALCULATE formulas for calculating the relevant values on filtered rows instead . Example:

var _selectedPI = SELECTEDVALUE(TableA[PI])
var _result = CALCULATE(SUM(TAbleB[Total Ready Days]),
KEEPFILTERS(TableB[PI]=_selectedPI))

While working on a similar issue, I have realized that the filtering didn't work using the filter measures due to the blank values returned by the statement: 

var _rowPI = FIRSTNONBLANK(TableB[PI],TRUE)

The solution I used is to return a non blank value when the above return blank using the COALESCE function.

Example:

Filter Table = 
var _selectedPI = SELECTEDVALUE(TableA[PI])
var _rowPI = COALESCE(MAX(TableB[PI],TRUE),"NONE")
return
IF(_rowPI= _selectedPI , 1, 0)

I hope this helps anyone who reads it.

[v-kkf-msft · Accepted solution]

Hi @karimm ,

There are two ways to achieve.
1. Apply the Filter Table measure to the visual level filter, and display values greater than 0.

2. Create two measures and put them into the value of the stacked column chart.

_Total Pending Days = 
CALCULATE(
  SUM(TableB[Total Pending Days]),
  FILTER(
    TableB,
    TableB[PI] in ALLSELECTED(TableA[PI])
  )
)

_Total Ready Days = 
CALCULATE(
    SUM(TableB[Total Ready Days]),
    FILTER(
        TableB,
        TableB[PI] in ALLSELECTED(TableA[PI])
    )
)

This is my PBIX file.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EeCaPax6FZxFgJeGpNRPh8...

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @karimm 

Why don't/can't you create a relationship between the tables on the PI columns?

Regards

Phil