Forum Discussion

NickProp28's avatar
NickProp28
Post Partisan
5 years ago
Solved

Needs historic data + refresh data

Dear Community,    I have been creating a report which needs to show the whole status for the products. For example raw data during the week 1.   During the week 2, after I refresh the raw...
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    Hi NickProp28 ,

     

    According to your description, similar results may not be achieved in powerbi. In powerbi, the best way is to continuously update the status results and keep records in the form of rows. Then create a column and use the CONCATENATEX function to show the entire change process in the card visual. If it is only updated in the original row, it will not be saved.

    just like:

    Col = 
CALCULATE (
    CONCATENATEX (  'Table','Table'[Status]  , "->" ),
    ALLEXCEPT ( 'Table', 'Table'[Product] )
)

     


    Best Regards,
    Henry


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

NickProp28's avatar
NickProp28
Post Partisan
5 years ago

Dear v-henryk-mstf ,

 

Thanks for your replied. 

But my main problem is, how to store the historical data when I perform the weekly refresh on the raw data.

Lets say I have one table with only 2 column. (Product and status)

Example in week 1, two product is still under 'pending' status.

 

In week 2, after I refresh my raw data. The chair has been approved. 

With your CONCATENATE function, I can show Chair status from Pending > Approved. 

 

But In week 3, when I refresh my raw data. The historic data for chair has been overwritten to Approved. 

So its only result "Approved" in chair status .

So would like to ask, isit anyway I can still keep my "Pending" status no matter how many time I refresh the raw data. Appreciated any help you may provided. 

v-henryk-mstf's avatar
v-henryk-mstf
Community Support
5 years ago

Hi NickProp28 ,

 

According to your description, similar results may not be achieved in powerbi. In powerbi, the best way is to continuously update the status results and keep records in the form of rows. Then create a column and use the CONCATENATEX function to show the entire change process in the card visual. If it is only updated in the original row, it will not be saved.

just like:

Col = 
CALCULATE (
    CONCATENATEX (  'Table','Table'[Status]  , "->" ),
    ALLEXCEPT ( 'Table', 'Table'[Product] )
)

 


Best Regards,
Henry


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