Forum Discussion

RobbieRalph's avatar
RobbieRalph
Frequent Visitor
2 years ago
Solved

Forcing Context

Hi There, I am struggling with context here.

 

Basically I am trying to create a measure to track Baseline performance (which i am basing on a simple method of if (price is greater than [max price] less controlled [what if parameter] discount threshold). It is simple enough to build this logic when the visual has the context of time (in this case it is an aligned week number). However, I am struggling to force the time context into the measure and return the same result (tried various approaches).

 

I previously spent a lot of time developing in SAP WebI and it was much easier to add context to your variables there - I could just say 'foreach [timeperiod]' I understand that DAX is very different but I am hoping that someone can help me to understand how to approach this.

 

This report is huge and in the interest of time I havent scrubbed and provided data - I am hoping that there is a short answer on how to apply additional context as I havent been able to achieve this.

 

The total in the table is correct, cannot get the card to match it.

 

Here are some of the measure approaches and results:

 

 

Baseline Store Count = 
var MP = 
[Max Price ($/Unit)]
var DP =
MP*(1-SELECTEDVALUE('Discount Threshold'[Discount Threshold]))
var mx =
  max(fact_iri[NPD Aligned Raw])
Return
//Returns correct value in table - returns total sum in card (incorrect)
//CALCULATE(sum(fact_iri[Stores Scanning Actual]),FILTER(fact_iri,fact_iri[Price (Account)]>DP))

//Returns correct value in Table - returns (blank) in card
//SUMX(FILTER(fact_iri,fact_iri[Price (Account)]>= DP && not(ISBLANK(fact_iri[NPD Aligned]))),fact_iri[Stores Scanning Actual])

//Returns correct value in Table - returns 0 in card
SUMX(FILTER(fact_iri, not(ISBLANK(fact_iri[NPD Aligned]))&& fact_iri[NPD Aligned Raw]<=mx),if(fact_iri[Price (Account)]>= DP,fact_iri[Stores Scanning Actual],0))

//Returns correct value in table - returns total sum in card (incorrect)
//CALCULATE(sumx(fact_iri,fact_iri[Stores Scanning Actual]),FILTER(fact_iri,fact_iri[Price (Account)]>DP))

//Returns correct value in table - returns (blank) in card 
//CALCULATE(sumx(fact_iri,if(fact_iri[NPD Aligned Raw]<=mx && fact_iri[NPD Aligned Raw]>0,fact_iri[Stores Scanning Actual])),FILTER(fact_iri,fact_iri[Price (Account)]>DP))

//Returns correct value in Table - returns 0 in card
//SUMX(CALCULATETABLE(SUMMARIZE(fact_iri,fact_iri[Account],fact_iri[NPD Aligned Raw],Dim_IRI_Prod_Attr[product_aztec_code],"Units",Sum(fact_iri[Units Actual AU]),"Stores",SUM(fact_iri[Stores Scanning Actual]),"Dollars",sum(fact_iri[Dollars Actual AU]),"Price",DIVIDE(sum(fact_iri[Dollars Actual AU]),sum(fact_iri[Units Actual AU]))),not(ISBLANK(fact_iri[NPD Aligned Raw])),fact_iri[NPD Aligned Raw]<=mx),if(not(ISBLANK([NPD Aligned Raw])) && [Price] > DP,[Stores],0))

 

 

 

  • Thanks Xiaoxin Sheng. I scrubbed my data and was building a new PBIX and couldnt replicate the issue - it was working as i intended it to. Upon further investigation it was actually a filter in the background applied to one visual but not the other.

2 Replies

    • RobbieRalph's avatar
      RobbieRalph
      Frequent Visitor

      Thanks Xiaoxin Sheng. I scrubbed my data and was building a new PBIX and couldnt replicate the issue - it was working as i intended it to. Upon further investigation it was actually a filter in the background applied to one visual but not the other.