Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.