Forum Discussion

Borjaveigasvh's avatar
Borjaveigasvh
Regular Visitor
6 years ago
Solved

Problem creating a calcualted column from diferent columns, Help needed

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...
average
columns
  • Anonymous's avatar
    Anonymous
    6 years ago

    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
         )
        )
    You need to tune the tabel defintion a bit, standard my FieldNumber column is called [Value].
     
    Hope this helps,
     
    Jan
     
    if this is a solution for you, don't forget to mark it as such. thanks
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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
     )
    )
You need to tune the tabel defintion a bit, standard my FieldNumber column is called [Value].
 
Hope this helps,
 
Jan
 
if this is a solution for you, don't forget to mark it as such. thanks
  • Borjaveigasvh's avatar
    Borjaveigasvh
    Regular Visitor
    6 years ago

    Thanks so much, I'll start to implement this solution.

    Sorry for the delay in answering.

     

    Best 

    Borja 

  • Borjaveigasvh's avatar
    Borjaveigasvh
    Regular Visitor
    6 years ago

    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 :

     
    Cx desviación = DIVIDE( CALCULATE(STDEV.P(UnPivotted[FieldValue]),filter(UnPivotted,FILTERS(MAESTRO[SOCIEDAD_UR])=UnPivotted[SOCIEDAD_UR])),CALCULATE(AVERAGE(UnPivotted[FieldValue]),filter(UnPivotted,FILTERS(MAESTRO[SOCIEDAD_UR])=UnPivotted[SOCIEDAD_UR]&&UnPivotted[FieldValue]<>BLANK()))
     
    does not work in all cases, when some values are missing it doesen't show the % and in some others it does. 
     
    Anyways many thanks again because yours is the correct path to find the solution and the problem may be as I mentioned in a relation 
     
    Regards
    Borja