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
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!
ThomasDay Happy to help! And I'm glad we got there eventually
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
That sounds really cool! Can you post some screenshots showing how it works in the actual report?? (Hiding the sensitive data )