The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I have a single fact table with multiple observed and modelled values from multiple assets, for comparison.
Some of the assets are measured in 2-minute time steps, others in 15-minutes (see image below).
I need to compare these with a multi-line chart, interpolating the 15-minute data to join the dots with lines. In excel I would simply plot a scatter with lines.
I've read this post from @Greg_Deckler tried various datetime formats, including a text timekey, explored custom visuals and am currently attempting to interpolate using a python script.
This is the final sticking point at the end of a very long road. Any advice would be gratefully received!
Hi @Anonymous ,
Have you ever tried to change the type of the X-axis to "Continuous"? Does this match your desired output?
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-kkf-ms,
thank you for responding.
Setting X to continuous was the first approach I made - the dropdown immediately switches back to categorical. I'm guessing my table structure is creating a conflict.
In your working example (thank you), there is a column for each asset ('tag'). In my fact table, there is a column "site" and another "tag" (concatenated as SourceTag in the image below) - values are returned in the column "value" (ignoring the fact these are zeros in the example below).
One post suggested basing the X axis on a text field "DateTimeKey" would enable a continuous X axis, but this still reverts back to categorical.
Apologies, I cannot post the model itself.
Thank you again for your help 🙂
Hi @Anonymous ,
You can try to fill in null values.
1. First pivot column SourceTag.
2. Then fill up/down the null values.
3. Select column DateTime and unpivot other columns.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-kkf-ms,
On step 1. pivot Source.Tag, I get the error below:
n.b. there is no row in the fact table for the blanks in my original post; there are 7 x two-minute timesteps for every 15-minute timestep.
I'm trying using a measure to hold the previous value, then referencing a second measure = if ( [Value] = BLANK() , [PreviousValue] , [Value] ), but need to work in conditions for source.tag and datetime.
I'll post a solution if I find one...
Thanks for your help.
Hi @Anonymous ,
According to your ideas, I found the following way to achieve.
First create a new calculation table "DateTime". And create a relationship with the Table.
DateTime = DISTINCT('Table'[DateTime])
Then create a measure, using the DateTime column in the DateTime table as the X axis of the line chart.
Measure =
var Pre_DT =
CALCULATE(
MAX('Table'[DateTime]),
FILTER(
ALLEXCEPT('Table', 'Table'[SourceTag]),
'Table'[DateTime] < MAX(DateTime[DateTime])
)
)
var Pre_Value =
CALCULATE(
SUM('Table'[Value]),
ALL(DateTime),
'DateTime'[DateTime] = Pre_DT
)
return
IF(
SUM('Table'[Value]) <> BLANK(),
SUM('Table'[Value]),
Pre_Value
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
82 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |