Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

question about Measure

Hi, 

i have encounter a problem i will appreciate your thoughts on

 

made an example to simulate:

 

The DB is looks like that 

 

Date

Item

Current Status Code

Last Month Status Code

Chg Code

Val

31/05/2017

Pen

S

M

SM

1490

30/06/2017

Pen

A

D

AD

1580

 

i want to create a Table in power BI the Shows:

 

Date

30/06/2017

 

 

 

 

Chg Code

 Val

Measure: Last Month Val

AD

1580

1490

 

i.e i am trying to create a measure that will show my the value of  Last Month  in the table of Chg Codes

the problem is i cant go back to DB with the Chg Code because it is not the same 

 

i have used a measure like Calculate(sum(Val),PREVIOUSMONTH(dates(date))  which works perfectly if the table has an item in the rows.  [ in guessi am looking for a formula that will take all items that have AD in their Chg Code for June 2017 and will create a virtual table for those items in the last month, and then sums their value without checking their Chg Code again)

 

any thoughts about how to perceive the result?

 

[ Just for an overview, i want to gather the past info of all items that has their code change between this month to the previous month from A to D)

 

hope i presented my problem clearly

thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    >>my DB is around 2-3 million rows and i need around 10 of these

    If you just want to get a summary records of each items, I'd like to suggest you use summarize function to group them and got the last val.

     

    >>do you recommend to use LOOKUPVALUE with big DB?

    As you mentioned , if you use measure to calculate through each records, it will spend the lots of system resources.

     

    Regards,

    Xiaoxin Sheng

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Perhaps add an ALLEXCEPT([Item]) to your filter clause? In theory that would eliminate the Chg Code filter context in your table visualization?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    As smoupre said, you can try to use other columns to group you records, then filter on current group and last date to find out the suitable records.

     

    Calculate column:

    LastMonth = LOOKUPVALUE([Val],[Item],[Item],[Date],DATE([Date].[Year],[Date].[MonthNo],1)-1)

     

    Measure:

    LastMonth 2 = 
var current_item=LASTNONBLANK(Sheet3[Item],[Item])
var current_date=MAX(Sheet3[Date])
return
LOOKUPVALUE(Sheet3[Val],Sheet3[Item],current_item,Sheet3[Date],DATE(YEAR(current_date),MONTH(current_date),1)-1)

     

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks,

      i was trying that but i was afraid it takes a lot of computing resources and the report will work slow.

      my DB is around 2-3 million rows and i need around 10 of these

      do you recommend to use LOOKUPVALUE with big DB?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi Anonymous,

         

        >>my DB is around 2-3 million rows and i need around 10 of these

        If you just want to get a summary records of each items, I'd like to suggest you use summarize function to group them and got the last val.

         

        >>do you recommend to use LOOKUPVALUE with big DB?

        As you mentioned , if you use measure to calculate through each records, it will spend the lots of system resources.

         

        Regards,

        Xiaoxin Sheng