Forum Discussion

askspepsi's avatar
askspepsi
Helper II
6 years ago
Solved

Bring Column value as header

hi all , 

i am new to the power bi, i have a query relating to DIRECTQUERY

I have a table in which the below sample data are there,

 

 

Timesheet TypeOT TypeOT in Mintues
Manual EntryRegular Hour480
Manual EntryNormal Hour120
Manual EntrySpecial Hour0

 

i wanted a output as,

 

 Regular Hour Normal Hour Special Hour
4801200

 

i have already tried the unpivot option but it say to change the storage mode to IMPORT which i can't do as it has very huge volumes of record in this one table alone.

So is there any other measures or creating another column or table with formula  that would help in achieving the output.

hi all , 

i am new to the power bi, i have a query relating to DIRECTQUERY

I have a table in which the below sample data are there,





Timesheet Type	OT Type	OT in Mintues
Manual Entry	Regular Hour	480
Manual Entry	Normal Hour	120
Manual Entry	Special Hour	0


i wanted a output as,



 Regular Hour	 Normal Hour	 Special Hour
480	120	0


i have already tried the unpivot option but it say to change the storage mode to IMPORT which i can't do as it has very huge volumes of record in this one table alone.

So is there any other mesures or creating another column or table with formula  that would help in achieving the output.



Thanks

regards

askspepsi

Thanks

regards

askspepsi

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi askspepsi ,

     

    You may refer the formula below.

    Table 2 =
DISTINCT (
    SELECTCOLUMNS (
        'Table',
        "Regular Hour", SUMX (
            FILTER ( 'Table', 'Table'[OT Type] = "Regular Hour" ),
            'Table'[OT in Mintues]
        ),
        "normal Hour", SUMX (
            FILTER ( 'Table', 'Table'[OT Type] = "normal hour" ),
            'Table'[OT in Mintues]
        ),
        "special Hour", SUMX (
            FILTER ( 'Table', 'Table'[OT Type] = "special hour" ),
            'Table'[OT in Mintues]
        )
    )
)

     

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

10 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    Matrix visual can help you to some extent. So, Try Matrix visual.

  • negi007's avatar
    negi007
    Community Champion
    6 years ago

    askspepsi  You can achive desired result using the Matrix Visual. For your reference, below refer to below picture

     

     

    Do let me know in case you are looking for something else.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi askspepsi ,

     

    You may refer the formula below.

    Table 2 =
DISTINCT (
    SELECTCOLUMNS (
        'Table',
        "Regular Hour", SUMX (
            FILTER ( 'Table', 'Table'[OT Type] = "Regular Hour" ),
            'Table'[OT in Mintues]
        ),
        "normal Hour", SUMX (
            FILTER ( 'Table', 'Table'[OT Type] = "normal hour" ),
            'Table'[OT in Mintues]
        ),
        "special Hour", SUMX (
            FILTER ( 'Table', 'Table'[OT Type] = "special hour" ),
            'Table'[OT in Mintues]
        )
    )
)

     

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • askspepsi's avatar
      askspepsi
      Helper II
      6 years ago

      Anonymous 

       thank you for your efforts this is want i required, i tried it out but iam getting the below expression

        "DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi askspepsi ,

         

        What's the data type of OT type column?

  • askspepsi's avatar
    askspepsi
    Helper II
    6 years ago

    hi all

    negi007 , Tahreem24 , amitchandak 

     

    Thank you for your response, actually i have already tried the matrix visualization but  i have eight table to get one report.

    in SQL we normally use  "Selecting column values as Field header name" like wise is there power bi is there like "Selecting column values as Field header name" because the value i mentioned before is only three values Normal, regular and special overtime.

     

    and in matrix i have tried but iam not able to repeat the name or emp id for one month (30days).