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

ThomasDay's avatar
ThomasDay
Impactful Individual
5 years ago

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's avatar
ThomasDay
Impactful Individual
5 years ago

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's avatar
    PaulDBrown
    Community Champion
    5 years ago

    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's avatar
      ThomasDay
      Impactful Individual
      5 years ago

      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's avatar
        PaulDBrown
        Community Champion
        5 years ago

        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