Forum Discussion
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....
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
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?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]))
