Forum Discussion

ThomasDay's avatar
ThomasDay
Impactful Individual
5 years ago
Solved

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....
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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

amitchandak's avatar
amitchandak
Super User
5 years ago

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