Forum Discussion

vaishalisah's avatar
vaishalisah
Regular Visitor
9 years ago
Solved

Multiply rows of two category based on different datetime frequency

Hello,   I need the following scenario to be achieved in Direct query mode. I was able to achieve it in Data Import mode but in Direct Query I see that the transformations and DAX formulas are quit...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    Hi vaishalisah,

     

    I need the following scenario to be achieved in Direct query mode. I was able to achieve it in Data Import mode.

    To achieve this requirement in direct query mode, you need to use query to do it. In your scenario, which datasource are you using?

     

    For the relational table, you ca use the query below.

    select a.TimeStamp,CAST(a.Cat_Value as varchar)+'*'+cast((select MAX(b.Cat_Value) from test5 b where b.Category='A' and b.TimeStamp<=a.TimeStamp) as varchar) as [Product(A,B)] from test5 a where a.Category='B'

     

    Regards,

    Charlie Liao

vaishalisah's avatar
vaishalisah
Regular Visitor
9 years ago

Hello,

 

Any thought on this? I need help on this as I am new to Power BI and trying to analyze the feasibility of replacing our current system with PowerBI. I tried creating measure but could not think of any way to refer back to the current/previous row value in that context.

 

I also tried to do pivot by category and then apply some logic to multiply. Again, couldn't find any way to get the values in columns with no values for the specific time stamp. Fill down/Up is not available for live connection queries.

 

I need to conclude whether this scenario is achievable in PowerBI or not.

 

Please help.

 

Regards,

Vaishali