Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 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
Solved! Go to Solution.
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.
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.
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?
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.
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.
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).
@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.
Proud to be a Super User!
Matrix visual can help you to some extent. So, Try Matrix visual.
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |