How to filter another table based on current pivot context?

[Anonymous]

Hi all,

Table A(1 side) and Table B(many side) are linked through code identity key.  Now I would like to count rows on Table B based on some fields in Table A, for e.g. I would like to count how many rows are there in table B when B[property1] = A[Aproperty1] & B[property2] = A[Aproperty2]. It would be quite easy to write in calculated columns:

=CALCULATE(COUNTROWS(TableB),FILTER(ALL(TableB),AND(TableB[property1]=TableA[Aproperty1],TableB[property2]=TableA[Aproperty2])))

But in pivot table measure, with TableA[Aproperty1] and TableA[Aproperty2] ticked in rows, if the measure is defined like this:

=CALCULATE(COUNTROWS(TableB),ALLEXCEPT(TableA,Aproperty1,Aproperty2))

Table B will be filtered first based on table A on the code primary key, then perform the countrows. So if there are codes in TableB that are not shown in table A, those values will not be counted.

What if I want to include those values?  I have tried something like below but with no luck

=CALCULATE(COUNTROWS(TableB),CROSSFILTER(TableB[code],TableA[code],none))

So how can I translate the calculated column to measure? Or how can I ignore the relation filter in measure?

Your help would be much appreciated

[v-shex-msft]

Hi @Anonymous,

You can try to use belwo formula if it suitable for your requirement:

Cross filter direction: both.

Measure:
CountRow = 
var temp= LASTNONBLANK(Sheet2[property2],[property2])
Return 
CALCULATE(COUNTROWS(Sheet2),FILTER(ALL(Sheet2),Sheet2[property1]=MAX(Sheet1[property1])&&Sheet2[property2]=temp))

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft

This works like a charm!

But I do not quite understand this expression,

What does this temp mean?

How does the context filter?

Can you help elaborate a little bit more？

Thanks very much!!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

>>What does this temp use for?

Temp is the current value on row content. Actual, when these function limit at row content level, I can use them to get current value.(max or other math functions used to get current number/date value, lastnonblank suitable with get text)

Detail description of formula:

1. Rows field property1 is from tableA, I can use lastnoblank function to get current value of row content;

2. Columns field is from tableB, I can use max function to get the current value of row content.

3. Based on above variables, I can filter the same records from all of tableB with the same property1 form tableA and the current property2 form tableB, then get the row count as the result.

Maybe above description is complex for understanding.

Regards,

Xiaoxin Sheng

[Anonymous]

Got it.

Thx very much!