cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasDay
Impactful Individual
Impactful Individual

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

TEMPTableIntersect.png

 

 

1 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:

New result.JPG

 

 Hope that helps!

 

Ps. PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

19 REPLIES 19
PaulDBrown
Super User
Super User

@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:
result.JPG

 

 

 

PS: PBIX file attached for reference





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






ThomasDay
Impactful Individual
Impactful Individual

@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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

New result.JPG

 

 Hope that helps!

 

Ps. PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

ThomasDay
Impactful Individual
Impactful Individual

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

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






ThomasDay
Impactful Individual
Impactful Individual

@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

@ThomasDay 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






ThomasDay
Impactful Individual
Impactful Individual

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

HeadToHeadShot1.pngHeadToHeadShot2.png

@ThomasDay 

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Super User
Super User

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






ThomasDay
Impactful Individual
Impactful Individual

@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
Impactful Individual
Impactful Individual

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

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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






ThomasDay
Impactful Individual
Impactful Individual

@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

@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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Greg_Deckler
Super User
Super User

@ThomasDay Try:

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

ThomasDay
Impactful Individual
Impactful Individual

@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
Super User
Super User

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

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.