Here is the link to my PBI file.
Here is my sample data in case you can't open my PBI file.
I have this calculated column (and other columns with the same logic and format):
Common Code = VAR _Code = CONTAINS( FILTER('Data', Data[Store] = "A"), Data[Code], Data[Code]) && CONTAINS( FILTER('Data', Data[Store]= "W"), Data[Code], Data[Code]) RETURN IF(_Code = TRUE,Data[Code], "")
and this measure to calculate Average Cost:
Average Cost = CALCULATE( AVERAGE( Data[Cost]), CALCULATETABLE( VALUES( Data[Common Code]), Data[Store] = "A" ), CALCULATETABLE( VALUES( Data[Common Code]), Data[Store] = "W" ))
This file is inteded to find common items among Stores and I feel like this file is quite near what I want it to be, except if I chose Store A, H, and K from the Store slicer, the chart didn't quite return the right result. Interpreting from what the chart returned, it makes sense to me that Store A & H share only code 1108, not 2 codes 1108 & 2014 like the tooltip showed when hovering over Store A. And that didn't even include Store K, meaning there's no common items among those 3 suppliers.
I'd like the chart to return ONLY the common items among the selected Stores. If there is NO common items among theselected Stores, return Blank.
Can anyone please advise how to go about this? Thank you so much!!!
You may add a condition measure to the visual level filter and set it=1.
Condition = VAR a = CALCULATE ( DISTINCTCOUNT ( Data[Store] ), ALLSELECTED ( Data[Store] ) ) VAR b = CALCULATE ( DISTINCTCOUNT ( Data[Store] ), ALLSELECTED ( Data ) ) RETURN IF ( a = b, 1 )
Hello @v-cherch-msft , thank you so much for your help! I'm wondering if you made any changes to the file you re-posted because I couldn't find anything different. And I did add the measure to the the Visual level filter, still, it gave the wrong result.
Ex: I selected A & H, the chart displayed this:
A & H only have Code 1108 in common and I still don't know how it gets Code 2014 there.
Can you advise please? Thank youuuuuuuu!!!!!!
I would suggest you add code column to get the correct value.
Hi @v-cherch-msft , thank you for your advices! That worked out!
Sorry for walking you around like this but I just realised it might not be a good idea to only show the month(s) they share the common code 1108 (which is July for the case of Store A & H)
|A||1108||21/07/2018||Same Month Same Code|
|H||1108||08/08/2018||Different Month Same Code|
|A||1108||12/11/2018||Different Month Same Code|
Question 1: If A & H are selected and that 1108 is the common code between the 2 Stores, is there a way to get the chart displayed all months, not just July? Because Code 1108 still appears in August (Store H) & in November (Store A).
Question 2: I checked the Average Cost, Average TAT, and Net Volume measures, they didn't seem to return the right answers. Is it possible to adjust them accordingly? I don't have that much of knowledge in DAX but I feel like currently those measures are only focusing on Store A & W or they're sort of fixed, not flexible when random Stores are selected from the Store slicer.
Can you please help? Thank you sooooo much!