Multiply rows of two category based on different datetime frequency

[vaishalisah]

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 quite limited.

Scenario:

I have a table with data of two of more categories. The data of two categories is logged at different frequency of time. I need to create a report or visualization which displays the product of values of two categories based on the timestamp value. For the timestamps where the less frequent data doesn't have a value, the most recent value available should be used. Following is the sample of data and expected output:

TimeStamp	Category	Cat_Value
2017-03-19 07:00:00.000	A	10
2017-03-19 07:15:00.000	A	10.5
2017-03-19 07:30:00.000	A	10.3
2017-03-19 07:00:00.000	B	5.1
2017-03-19 07:05:00.000	B	5.3
2017-03-19 07:10:00.000	B	5.4
2017-03-19 07:15:00.000	B	5.2
2017-03-19 07:20:00.000	B	5.6
2017-03-19 07:25:00.000	B	5
2017-03-19 07:30:00.000	B	5.5

Output Expected
	Product (A,B)
2017-03-19 07:00:00.000	5.1 * 10
2017-03-19 07:05:00.000	5.3 * 10
2017-03-19 07:10:00.000	5.4 * 10
2017-03-19 07:15:00.000	5.2 * 10.5
2017-03-19 07:20:00.000	5.6 * 10.5
2017-03-19 07:25:00.000	5 * 10.5
2017-03-19 07:30:00.000	5.5 * 10.3

Please suggest on how it can be done.

Thanks,

Vaishali

[v-caliao-msft · Accepted solution]

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]

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