Measure with temp table to check if fact table values are in temp table

[jimbob2285]

Hi

I'm trying to create a measure that I can filter my fact table with, but I'm struggling.

My fact table is:

Project	Sale	Quarter	Cust	Value
P1	S1	QTR3	C1	100
P1	S2	QTR2	C1	100
P2	S3	QTR3	C2	100
P2	S4	QTR2	C2	100
P3	S5	QTR2	C3	100
P3	S6	QTR2	C3	100
P4	S7	QTR3	C4	100
P4	S8	QTR1	C4	100

I want to be able to filter the table by a quarter (QTR) selected in a slicer… which I can do, by applying the following measure as a table filter:

Selected_QTR = IF(SELECTEDVALUE(TableSalesOrder[QTR]) = MAX(FactTable[QTR]), 1, 0)

Project	Sale	Quarter	Cust	Value	Selected QTR
P1	S1	QTR3	C1	100	1
P2	S3	QTR3	C2	100	1
P4	S7	QTR3	C4	100	1

However, I then want to create a second measure which I can use to return all rows for the projects that are present in the selected quarter, regardless of which quarter they’re in… I.e. “Show me all transactions for the projects that were transacted in QTR3” for instance.  The resulting filtered table should be as below, excluding P3, which has no transactions in QTR3:

Project	Sales	Quarter	Cust	Value	Selected QTR	Selected QTR Project
P1	S1	QTR3	C1	100	1	1
P1	S2	QTR2	C1	100	0	1
P2	S3	QTR3	C2	100	1	1
P2	S4	QTR2	C2	100	0	1
P4	S7	QTR3	C4	100	1	1
P4	S8	QTR1	C4	100	0	1

I’m doing this with measures because I’m aware that calculated tables and columns are not dynamic, i.e. they are calculated at data load and do not respond to slicer selection:

So, I’m trying to create a measure that creates a temp table, which is filtered by the selected QTR and then looks to see if the project in the fact table is in the temp table… But this is where I’m having problems:

Selected_QTR_Projects =
VAR MyFilterTable = FILTER(FactTable, FactTable[QTR] = SELECTEDVALUE(TableSalesOrder[QTR]))
RETURN
IF(MAX(FactTable[Project]) IN {MyFilterTable[Project]}, 1, 0)

It returns the error: “Cannot find table 'MyFilterTable'.”

• I realise that the IN operator is designed to look at a list of values and not a table column, so that part of it’s probably not right, but why can’t it find the temp table
• If I amend it to return the max project from the temp table, I get the error: “Table variable 'MyFilterTable' cannot be used in current context because a base table is expected.”

I’m a bit lost and don’t know where to go from here, can anyone please help

I've also linked the file I'm working on if that helps: Quarter Projects 

Cheers

Jimbob

[Anonymous · Accepted solution]

Hi @jimbob2285 ,

Thanks for MNedix's reply!
And @jimbob2285 , I modified MNedix's reply and it works fine now:

Selected_QTR_Projects = 
VAR MyFilterTable = SUMMARIZE(FILTER(ALL(FactTable), FactTable[Quarter] = SELECTEDVALUE(TableSalesOrder[Quarter])),FactTable[Project])
RETURN
IF(MAX(FactTable[Project]) IN MyFilterTable, 1, 0)

Output:

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

[jimbob2285]

Thanks v-junyant-msft, that works perfectly

[Kedar_Pande]

You're on the right track. You can modify your measure:

Selected_QTR_Projects =
VAR MyFilterTable = CALCULATETABLE(
VALUES(FactTable[Project]),
FactTable[QTR] = SELECTEDVALUE(TableSalesOrder[QTR])
)
RETURN
IF(
CONTAINS(MyFilterTable, FactTable[Project], MAX(FactTable[Project])),
1,
0
)

If the project is found, it returns 1; otherwise, it returns 0.

[jimbob2285]

This really looks like it should work, but it's still only putting a 1 in the rows for the selected quarter, any ideas why?

[Kedar_Pande]

Can you try the updated logic:

Selected_QTR_Projects =
VAR MyFilterTable = CALCULATETABLE(
VALUES(FactTable[Project]),
FactTable[QTR] = SELECTEDVALUE(TableSalesOrder[QTR])
)
RETURN
IF(
CALCULATE(
COUNTROWS(MyFilterTable),
ALLEXCEPT(FactTable, FactTable[Project])
) > 0,
1,
0
)

[jimbob2285]

That gives me the same result too... I wish I could find a way to share my PBIX file with you, but I don't think the link is accessible outside of my organisation

[jimbob2285]

when I amend it to:

Selected_QTR_Projects5 = 
VAR MyFilterTable = CALCULATETABLE(
VALUES(FactTable[Project]),
FactTable[QTR] = SELECTEDVALUE(TableSalesOrder[QTR])
)
RETURN

CALCULATE(
COUNTROWS(MyFilterTable),
ALLEXCEPT(FactTable, FactTable[Project])
)

It doesn't even return a result for the rows that aren't in the selected QTR

[MNedix]

Heya,

The link is not accessible, at least not to me.

However, you may want to try using the SUMMARIZE function to create the MyFilterTable. Something along the line:

Selected_QTR_Projects =
VAR MyFilterTable = SUMMARIZE(FILTER(FactTable, FactTable[QTR] = SELECTEDVALUE(TableSalesOrder[QTR])),FactTable[Project])
RETURN
IF(MAX(FactTable[Project]) IN {MyFilterTable[Project]}, 1, 0)

If this solved your problem then please mark it as the solution so others can see it.

Cheers,