Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
hi Guys
I am struggling to model data which is being imported quarterly, from Flat file CSV data set.
The columns are in 'array' type format, and new columns are expected on quarterly refresh takes place
so score[0] is corresponding to createdon[0] column and score[1] is corresponding to createdon[1] column, if data exists...
Product_id | score[0] | score[1] | score[2] | score[3] | createdOn[0] | createdOn[1] | createdOn[2] | createdOn[3] |
1 | 2 | 01/11/2020 | ||||||
2 | 4 | 6 | 7 | 01/12/2020 | 01/13/2020 | |||
3 | 6 | 8 | 9 | 10 | 01/13/2020 | 01/14/2020 | 01/15/2020 | 01/16/2020 |
how can i model, to display e.g. average product Score, against a Date Dimension?
(remember, that new 'array' style columns will be added, in every new feed)
thank you
Hi @usman1 ,
First, You should Transform the data to unpivot the score and createdOn columns in power query.
Then create a date dimension table and use the following formula to ask for the average value.
Average Score =
AVERAGEX (
FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'Table'[Date] ) ),
'Table'[Value]
)
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Adam
The createdOn[0] and Score[0] should be on same line
and The createdOn[1] and Score[1] should be on same line
Rest of the line need to be deleted
Is it possible, to only display relevant rows?
thank you again in advance
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
89 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |