Forum Discussion

dbrandone's avatar
dbrandone
Helper IV
4 years ago

Historical Daily calculations based on dynamic fields

Hi everyone,   I ran into a roadblock with one request I received from a department. I wanted to see if another set of eyes could shed some light on the possibilities.   The department needs to t...
Need Help
dbrandone's avatar
dbrandone
Helper IV
4 years ago

lbendlin 

Due to the highly sensitive nature of the data we work with, the dataset that I sent is watered down and limited versus the data that I am actually playing with. I do use an extensive Date Table and all of the dates are connected to the date table and when I need to use a particular date that is not the primary connection to the date table, I do use "USERELATIONSHIP". So, what I am using on the backend does mimic somewhat of what you are describing. 

 

I do not mind going back to the department and telling them that their datasource design is not condusive to this particular metric, but wanted to at least reach out the experts first to see if it is possible. If you need me to add my date table to the example, then I can do that. 

 

I have written a few measures that play off of SELECTEDVALUE ('Date' [Date]) within a table view but I still run into the each row only being counted once even if it is in inventory for 3 days. I understand why this is happening, but unsure if there is a way to do it. I was thinking that maybe using Power Query to build an inventory table with each order and the dates it is in inventory, but that would make the OrderId and the date column of that table a "Many" situation with both and don't know if that would work with this situatiuon. Thoughts?

lbendlin's avatar
lbendlin
Super User
4 years ago

It might work for smaller data volumes. Custom logic and scalability don't usually go well together.

 

Which business decisions is the report driving?  Can the intelligence required for making these decisions be gleaned in some other way?

  • dbrandone's avatar
    dbrandone
    Helper IV
    4 years ago

    lbendlin 

    This metric is for one of the departments yearly goals. I understand I am pushing the limits, if not over, what Power BI can do, but the database that I have access to is what it is (For now!!). 

     

    I decided for a temporary work around by creating a write back to SQL process. I created the equivilant of the DAX measure in Power Query within a duplicated table just for this process. I then built a table in a SQL instance I have and when the report refreshes, if a duplicate is not found, then it writes the data I need, and then the data refreshes on the Power BI end to reflect it. It is working so far, so fingers crossed it holds up well until l find another solution within Power BI or on the database side(not holding my breath).

     

    Thanks for your assistance with this

      • dbrandone's avatar
        dbrandone
        Helper IV
        4 years ago

        lbendlin 

        yeah, I figured that into the query I wrote. I have it check for duplicate entries first and if no duplicate found, then write. Else, just return the table without writing.