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
    4 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.

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

Hi NickProp28 ,

 

The incrementally refreshed data is added in the form of rows on the basis of historical data. According to your description, even if the refreshed status is changed, it will only replace the original status, and will not be updated to the form of "Pending>Approved" in the field.

 

If you can create a column in the database to display the latest status, you can use the CONCATENATE function in powerbi to display this status:

Col =
IF (
    [Status] = [Up_status],
    "Pending",
    CONCATENATE ( [Status] & ">", [Up_status] )
)

 


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.

  • NickProp28's avatar
    NickProp28
    Post Partisan
    4 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
      4 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.