Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Good Mornig all,
I have a doubt, Iknow I can either unpivot or create a column in M, but my problem is, that I need to have the data pivoted and some of the collumns come from a calculated column not from de dataset.
I have a N assets in the datast. sevreal columns give us diferent valuations for that asset (appraisal, appraisal2, appraisal3, appraisal4 market value, etc)
Therefore to get a variation of coeficient, I need to make a calculation that gets all the columns standard deviation and divide it by the average of their value.
Does enyone have an idea for this matter?
Many thanks
Borja Veiga
Solved! Go to Solution.
Hi @Borjaveigasvh
You can "unpivot" with dax as long as you don't expect dynamic creation of fields. Something like this will create a calcuated (unpivotted) table and from there on you can use the normal static functions.
UnPivotted =
GENERATE(
CROSSJOIN(
GENERATESERIES(1,5,1), // 5 the number appraisal fields you have
ALL('Table'[Asset])
),
var _Asset = 'Table'[Asset]
var _Field =
switch(
TRUE(),
[VALUE]=1,LOOKUPVALUE('Table'[f1],'Table'[Asset],_Asset), // your appraisal fields
[VALUE]=2,LOOKUPVALUE('Table'[f2],'Table'[Asset],_Asset),
[VALUE]=3,LOOKUPVALUE('Table'[f3],'Table'[Asset],_Asset),
[VALUE]=4,LOOKUPVALUE('Table'[f4],'Table'[Asset],_Asset),
[VALUE]=5,LOOKUPVALUE('Table'[f5],'Table'[Asset],_Asset)
)
return
ROW(
"FieldValue",_Field
)
)
Hi @Borjaveigasvh
You can "unpivot" with dax as long as you don't expect dynamic creation of fields. Something like this will create a calcuated (unpivotted) table and from there on you can use the normal static functions.
UnPivotted =
GENERATE(
CROSSJOIN(
GENERATESERIES(1,5,1), // 5 the number appraisal fields you have
ALL('Table'[Asset])
),
var _Asset = 'Table'[Asset]
var _Field =
switch(
TRUE(),
[VALUE]=1,LOOKUPVALUE('Table'[f1],'Table'[Asset],_Asset), // your appraisal fields
[VALUE]=2,LOOKUPVALUE('Table'[f2],'Table'[Asset],_Asset),
[VALUE]=3,LOOKUPVALUE('Table'[f3],'Table'[Asset],_Asset),
[VALUE]=4,LOOKUPVALUE('Table'[f4],'Table'[Asset],_Asset),
[VALUE]=5,LOOKUPVALUE('Table'[f5],'Table'[Asset],_Asset)
)
return
ROW(
"FieldValue",_Field
)
)
Hello JustJan I have to thank you, after implementing your solution, it does work!
I have some issues, because since the appraisals and values come from different tables,when In implement this formula :
Thanks so much, I'll start to implement this solution.
Sorry for the delay in answering.
Best
Borja
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |