Forum Discussion

Fuhrer786's avatar
Fuhrer786
Frequent Visitor
7 years ago
Solved

Change Column value based on category value

Hi Users,

 

I have 3 products and 5 forecasted models showing 5 diff forecasted values for each product. However in the table chart I only put my top 3 models which I have already decided based on prior data.The sample data looks like below:

 

Product  Year  Month  Fcstvalue1  FcstValue2  FcstValue3 

A             2019   7        100               200                 125

B              2019  7         2000             5000              3500

c              2019  7          150            200                300

 

What I know is for product C ,none of my top 3 models are accurate as compared to 4th model. So, I would like to put condition that when product=C then fcstvalue1=fsctvalue4 else fsctvalue1 but I am not able to quite get that DAX query right.

 

Note: The product field is coming from different table and all foreacasted values are in different  columns in another forecast table like Fsctvalue1,Fsctvalue2,Fsctvalue3,Fsctvalue4 and Fsctvalue5.

 

Thanks in Advance!

Need Help
  • Fuhrer786's avatar
    Fuhrer786
    7 years ago

    Cmcmahan It worked by altering a bit. Thanks a ton!

     

    Model1BestValue = IF(SELECTEDVALUE(Product[ProductID])="C",CALCULATE(sum(Forecast[Fsctvalue4])),CALCULATE(sum(Forecast[Fsctvalue1])))

4 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    Sure, this is possible.  What sort of visual are you putting this in? A table? Matrix? Graph of some sort?

     

    If you're using a matrix, you can use this measure:

    Fcstvalue1SmartReplace = IF( SELECTEDVALUE(Product[ProductID]) = "C", SELECTEDVALUE(Forecast[fsctvalue4]), SELECTEDVALUE(Forecast[fsctvalue1]))

    And replace the FcstValue1 in the values bucket of the visual with this measure.  Note that I'm assuming for any given year/month/product combination, there's only one value for fsctvalue4.  

    • Fuhrer786's avatar
      Fuhrer786
      Frequent Visitor
      7 years ago

      Thanks for this solution, but the forecast4 value changes every year-month for product and I am using line and clustered chart

    • Fuhrer786's avatar
      Fuhrer786
      Frequent Visitor
      7 years ago

      Cmcmahan It worked by altering a bit. Thanks a ton!

       

      Model1BestValue = IF(SELECTEDVALUE(Product[ProductID])="C",CALCULATE(sum(Forecast[Fsctvalue4])),CALCULATE(sum(Forecast[Fsctvalue1])))
      • Cmcmahan's avatar
        Cmcmahan
        Resident Rockstar
        7 years ago

        Glad you got it working!

         

        Does it make sense to sum multiple forecast values?  For example, if I calculate this in a context where I have all products selected, would I want to SUM all the forecasts together? 

         

        If not, I would replace your SUM aggregations with SELECTEDVALUE expressions, or another aggregation that makes sense.