Using IN VALUES (Memory Table[Column]) ...can't find table!

[ThomasDay]

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

[PaulDBrown]

@ThomasDay 

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

[ThomasDay]

@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

[PaulDBrown]

@ThomasDay 

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)

[PaulDBrown · Accepted solution]

@ThomasDay 

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

[ThomasDay]

@PaulDBrown  Yes, that works great.  Thank you very much for your time and multiple directions, attacking style. 

Thinking about my specific example, seems to me that the only measure really needed is the filter measure...Any calculations I make for each provider will be handled by the matrix, and if the rowcount for the common zips is 1--there's a zip, so job done.

Whew, that's a nice solution!

 

[PaulDBrown]

@ThomasDay Happy to help! And I'm glad we got there eventually

[ThomasDay]

@PaulDBrown I thought you'd like to see what this has morphed to.  Here's the filter for the Matrix--shows rows where a zip code is in the measure. 
If only two providers are selected, I present a slicer...with choices for what to display.  Depending on the choices, you can show all, show only those in common, those zips served by provider 1 only or zips served by provider 2 only.  And if not two providers--show all.

OverlapOrNon_ZipCodes = 
	VAR _JoinType = IF(HASONEVALUE(OverlapNon[JoinType]),VALUES(OverlapNon[JoinType]),"Overlap")
	VAR _TempTable1 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360163),
			HSAFAllYears[ZipCode])
    VAR _TempTable2 = Summarize( FILTER(HSAFAllYears,HSAFAllYears[Provider] = 360132),
			HSAFAllYears[ZipCode])
    RETURN
    IF([ProvCount] = 2,
    IF(_JoinType = "ALL",  UNION(_TempTable1, _TempTable2),
	IF(_JoinType =  "Overlap", NATURALINNERJOIN(_TempTable1, _TempTable2),
	IF(_JoinType =  "1 only", EXCEPT(_TempTable1, _TempTable2),
	IF(_JoinType =  "2 only", EXCEPT(_TempTable2, _TempTable1), 
		NATURALINNERJOIN(_TempTable1, _TempTable2))))),
    UNION(_TempTable1, _TempTable2))

 Thanks!  Tom

[PaulDBrown]

@ThomasDay 

That sounds really cool! Can you post some screenshots showing how it works in the actual report?? (Hiding the sensitive data )

[ThomasDay]

@PaulDBrown This feature is rough yet.  Choose locations where patients originate (Every Medicare patient from every zip code since 2013 is available), Choose destinations of care by state or county...or refine by Provider.  If there are only two providers...do the head to head.  There's a tool tip on the slicer--but it's tricky to make it clear and fit the space.  Anyway, with that proviso--here are two screenshots.  On the left--zips from both are shown.  You can see that both serve all but 1 zip in Hamilton...and that then focuses a plan process---and what you do depends on which side of the competition you're on.  Anyway...it's very fast so pretty cool.  I do wish I could make the slicer disappear if there were more than 2 providers.  And I'll put a better tool tip on the slicer...but this is a start.

[PaulDBrown]

@ThomasDay 

Excellent work! A great example of the flexibility PBI offers and an intelligent deployment of options for the users!

[PaulDBrown]

@ThomasDay 

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))

[ThomasDay]

@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

[ThomasDay]

@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?

[PaulDBrown]

@ThomasDay 

It depends how your model is set up, and which fields are used in the visual. 

Maybe 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]), TREATAS(INTERSECT(_TempTable1, _TempTable2), HSAFAllYears[ZipCode]))

[ThomasDay]

@PaulDBrown This really looks promising...the documentation on TREATAS is exactly what I'm looking for and it doesn't return an error....  Let me play around with this and see what I can make of it since you correctly point out it depends on the model.  Thank you.  Tom

[PaulDBrown]

@ThomasDay 

Just in case, you may have to swap the order of the tables in the TREATAS expression from

 TREATAS(INTERSECT(_TempTable1, _TempTable2), HSAFAllYears[ZipCode]))

 
to

TREATAS(VALUES(HSAFALL[ZipCode]), INTERSECT(_TempTable1, _TempTable2))

Edit: now that I am on a PC (instead of my phone) I'll give your sample data a shot

[Greg_Deckler]

@ThomasDay Try:

CALCULATE(SUM(HSAFAllYears[Charges]), HSAFAllYears[ZipCode] IN DISTINCT(SELECTCOLUMNS(_TEMP_ZipCodes,"ZipCode",[ZipCode])))

[ThomasDay]

@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

[amitchandak]

@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))