cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
trdoan Member
Member

Modify Measure & Calculated Column to accept Variable instead of using Fixed values

Hi everyone,

 

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!!!

 

 

 

 

 

  

 

 

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Modify Measure & Calculated Column to accept Variable instead of using Fixed values

Hi @trdoan 

 

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 )

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trdoan Member
Member

Re: Modify Measure & Calculated Column to accept Variable instead of using Fixed values

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:

1.PNG

 

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!!!!!!

Community Support Team
Community Support Team

Re: Modify Measure & Calculated Column to accept Variable instead of using Fixed values

Hi @trdoan 

 

I would suggest you add code column to get the correct value.

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trdoan Member
Member

Re: Modify Measure & Calculated Column to accept Variable instead of using Fixed values

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)

 

StoreCodeDate 
A110821/07/2018Same Month Same Code
H110830/07/2018
H110808/08/2018Different Month Same Code
A110812/11/2018Different 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!