Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
askspepsi
Helper II
Helper II

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

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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]
        )
    )
)

 1.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

10 REPLIES 10
v-jayw-msft
Community Support
Community Support

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]
        )
    )
)

 1.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

@v-jayw-msft 

 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."

Hi @askspepsi ,

 

What's the data type of OT type column?

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

OT type column data type is Decimal Numbers

and OT in minutes data type is also Decimal Numbers.

Hi @askspepsi ,

 

This error means the data types on both sides of the equal sign are different so cannot be compared. Since the OT type column data type is Decimal Numbers, please try remove the quotes at the left of the equal sign.

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

hi @v-jayw-msft ,

 

First of all i am really sorry for the delay in replying as i got stuck with work loads.

Thank for the output which you have provided, its really provides the sum of hours as output.

 

- Can i ask how can i get the output for employee wise and month wise.

- The employee and months are in two different table respectively.

 

 

regards

askspepsi.

askspepsi
Helper II
Helper II

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).

negi007
Community Champion
Community Champion

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

 

Login_hrs.PNG

 

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




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

Tahreem24
Super User
Super User

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

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
amitchandak
Super User
Super User

@askspepsi , if you use Show on Row in the matrix, you should get this

refer: https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.