Forum Discussion
Using IN VALUES (Memory Table[Column]) ...can't find table!
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
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
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
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)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
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!