Forum Discussion
Using IN VALUES (Memory Table[Column]) ...can't find table!
Hello Fellow Daxers,
I have a table which I want to filter based on a memory table column in a calculation. I build a list of zipcodes that are shared by two providers...and I want to analyze them. This measure, in a matrix, would show the calculation of charges for each provider in the common zipcodes. It returns an error that it cannot find the table _TEMP_ZipCodes.
UseTemp_ValuesFilter =
VAR _TempTable1 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360163),
HSAFAllYears[ZipCode])
VAR _TempTable2 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360132),
HSAFAllYears[ZipCode])
VAR _TEMP_ZipCodes = INTERSECT(_TempTable1, _TempTable2)
Return
CALCULATE(SUM(HSAFAllYears[Charges]), HSAFAllYears[ZipCode] IN VALUES(_TEMP_ZipCodes[ZipCode]))
Cannot find table '_TEMP_ZipCodes'.
I know that _TEMP_ZipCodes creates a table if I isolate it and create a table as shown below
_TEMP_ZipCodes =
VAR _TempTable1 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360163),
HSAFAllYears[ZipCode])
VAR _TempTable2 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360132),
HSAFAllYears[ZipCode])
RETURN
INTERSECT(_TempTable1, _TempTable2)
Here's a screenshot of the result . So is there a way to use it in a FILTER of the CALCULATION? Thank you very much in advance! Tom
To see/get excel file with sample data from my Dropbox
Using the PBIX file you attached in your last post.
If you want to display the matrix as per your last post, use the measure for the filter pane:
Measure for Filter Pane = VAR _TempTable1 = CALCULATETABLE(VALUES(Sheet1[ZipCode]), FILTER(Sheet1,Sheet1[Provider] = "360163")) VAR _TempTable2 = CALCULATETABLE(VALUES(Sheet1[ZipCode]), FILTER(Sheet1,Sheet1[Provider] = "360132")) Return COUNTROWS(INTERSECT(_TempTable2,_TempTable1))(The measure used in the values bucket is a simple SUM of "Charges"):
Sum of charges = SUM(Sheet1[Charges])Applying the [Measure for filter pane] you get this:
Hope that helps!
Ps. PBIX file attached
19 Replies
ThomasDay , Try like
UseTemp_ValuesFilter =
VAR _TempTable1 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360163),
HSAFAllYears[ZipCode])
VAR _TempTable2 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360132),
HSAFAllYears[ZipCode])
VAR _TEMP_ZipCodes = INTERSECT(_TempTable1, _TempTable2)
Return
CALCULATE(SUM(HSAFAllYears[Charges]), HSAFAllYears[ZipCode] IN VALUES(_TEMP_ZipCodes))ThomasDay Try:
CALCULATE(SUM(HSAFAllYears[Charges]), HSAFAllYears[ZipCode] IN DISTINCT(SELECTCOLUMNS(_TEMP_ZipCodes,"ZipCode",[ZipCode])))Greg_Deckler Thanks for the quick reply...it returns the error: A function 'SELECTCOLUMNS' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
I've not looked at the function definition etc to see if I can unpack the error message, but send it along for you to see. Tom
Try:
UseTemp_ValuesFilter = VAR _TempTable1 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360163), HSAFAllYears[ZipCode]) VAR _TempTable2 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360132), HSAFAllYears[ZipCode]) Return CALCULATE(SUM(HSAFAllYears[Charges]), INTERSECT(_TempTable1, _TempTable2))PaulDBrown That doesn't yield an error message! Let me check out the results to see if it did what it seems like it should! Thanks...will post in a bit. Tom
PaulDBrown The result of
CALCULATE(SUM(HSAFAllYears[Charges]), INTERSECT(_TempTable1, _TempTable2))
is a blank. Seems like the INTERSECT filter doesn't reference HSAFALLYears so I guess that makes sense. Any other ideas?
OK. Checking your sample data, you may have been getting blanks beacuse it seems no ZIP Codes actually matched. I've added further rows with common zip codes between providers just to make sure. The model is a single table (No dimension tables used)
1) To check by filtering the list of zip codes in the filter pane, by establishing this measure value as "1":Filter Pane = VAR _TempTable1 = CALCULATETABLE(VALUES(Sheet1[ZipCode]), FILTER(Sheet1,Sheet1[Provider] = "360163")) VAR _TempTable2 = CALCULATETABLE(VALUES(Sheet1[ZipCode]), FILTER(Sheet1,Sheet1[Provider] = "360132")) VAR reslt = INTERSECT(_TempTable1, _TempTable2) Return COUNTROWS(reslt)2) to get the actual sum without the filter applied in the filter pane:
Charges by Common ZIPCodes = VAR _TempTable1 = CALCULATETABLE(VALUES(Sheet1[ZipCode]), FILTER(Sheet1,Sheet1[Provider] = "360163")) VAR _TempTable2 = CALCULATETABLE(VALUES(Sheet1[ZipCode]), FILTER(Sheet1,Sheet1[Provider] = "360132")) Return CALCULATE([Sum Charges], INTERSECT(_TempTable1,_TempTable2))And this is the result:
PS: PBIX file attached for reference
PaulDBrown Great idea to start with the data and matrix only. I should do that every time.
So...I put the Sheet1 from the Link I sent into a table...and then using the simplified measure you sent can see it does indeed "work" in that there is no error.Boy-forget the model I attached before this edit...and if I just put a filter in your third matrix for values >0, I have the answer. My model has issues I must say....so I'm going to remove the link and take a further look...clumsy. That said, if I could possibly figure out why the intersect doesn't yield a short list, that would be great.
Tom
Apologies since I am not in front of a PC at the moment. I'll dive deep on your file when I get home.
However, can you try the measure I posted to be used in the Filter Pane (measure number 1) and use it on the left table in your last post? Select the visual, add the measure in the filter pane for that visual and establish the filter for a value of 1. (For this, the measure for values can be the simple sum of charges btw)
