Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

how to get value from another table

Hi i have two table, one saletypsinfo like this:

datesalestype
2021-01-01new year sales
2021-01-09No sales
2021-02-15feb sales
2021-02-20No sales
2021-03-01Season sales

 

and a facttables with date and sales like this:

datesales
2021-01-01100
2021-01-02150
2021-01-03400
2021-01-04200
2021-01-05400
2021-01-06150
2021-01-07150
2021-01-08400
2021-01-09400
2021-01-10100
2021-01-11400
2021-01-12400
2021-02-15200
2021-02-16400
2021-02-17150
2021-02-18400
2021-02-19200
2021-02-20400
2021-02-21400
2021-02-22400
2021-02-23100
2021-03-01400
2021-03-02400
2021-03-03200
2021-03-04400

 

I want to add the salestype column into the facttables to track what kind of sales is it, do anyone have idea?

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi Anonymous ,

     

    Agree with the solution given by amitchandak  and do the following test:

    new column = 
var _max = maxx(filter(Saletypes, Saletypes[date] <='Fact'[date]),Saletypes[date])
return
maxx(filter(Saletypes, Saletypes[date] = _Max ),Saletypes[salestype])


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

     

    Best Regards,
    Henry

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a new column like this in sales table sales

     

    new column =
    var _max = maxx(filter(saletypsinfo, saletypsinfo[Date] <=sales[Date]),saletypsinfo[Date])
    return
    maxx(filter(saletypsinfo, saletypsinfo[Date] = _Max ),saletypsinfo[salestype])

  • jmontes1810's avatar
    jmontes1810
    Frequent Visitor
    5 years ago

    I think a LookUpValue in a new column on your facttables table is a easier way to do it.
    Something like this....

    New Column = LOOKUPVALUE(saletype[saletypsinfo],date[saletypsinfo],date[facttables])
     
    *slaestypeinfo must cotain unique values in date column to work
  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Agree with the solution given by amitchandak  and do the following test:

    new column = 
var _max = maxx(filter(Saletypes, Saletypes[date] <='Fact'[date]),Saletypes[date])
return
maxx(filter(Saletypes, Saletypes[date] = _Max ),Saletypes[salestype])


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

     

    Best Regards,
    Henry

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you it works fine with your solution.