Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I amtrying to create a power bi reporte using SQL Server Direct Query mode. I have the below columns with some sample values
Number | Company | Status | Date |
1 | XXX | GD | 29/09/2014 15:58:27 |
1 | XXX | UC | 29/09/2014 12:43:39 |
2 | YYY | GD | 30/09/2014 15:58:27 |
2 | YYY | UD | 30/09/2014 12:43:39 |
3 | ZZZ | GD | 27/09/2014 15:58:27 |
3 | ZZZ | UC | 27/09/2014 12:43:39 |
Need to create table or matrix to show the below output. So need help in achiving the below output in Power BI
Number | Company | Status | Hours Difference Between | |
GD | UC | |||
1 | XXX | 29/09/2014 15:58:27 | 29/09/2014 12:43:39 | 3:15 |
2 | YYY | 30/09/2014 15:58:27 | 30/09/2014 12:43:39 | 3:15 |
3 | ZZZ | 27/09/2014 15:58:27 | 27/09/2014 12:43:39 | 3:15 |
Any help in achiving this Hours Difference Between calculation in Power or SQL.
I tried in SQL and got the below output
Number | Company | GD Date | UC Date | Hours Difference Between |
1 | XXX | 29/09/2014 15:58:27 | NULL | |
1 | XXX | NULL | 29/09/2014 12:43:39 | |
2 | YYY | 30/09/2014 15:58:27 | NULL | |
2 | YYY | NULL | 30/09/2014 12:43:39 | |
3 | ZZZ | 27/09/2014 15:58:27 | NULL | |
3 | ZZZ | NULL | 27/09/2014 12:43:39 |
Thanks,
Maya
Solved! Go to Solution.
Hi @Maya2988,
One way to achieve this is to create a new table based on the original one to get the expected structure and also set the right format for duration column:
Hey @Maya2988,
Just include that column in SUMMARIZE function:
table1 =
ADDCOLUMNS(
SUMMARIZE(
tabA,
tabA[Company],
tabA[Number]
)
, "GD time", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "GD")
, "UC time", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "UC")
, "duration", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "GD") - CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "UC")
)
Hi @wini_R
Thanks for the help. This helps me a lot and learnt a lot.Please tell me how to add Number also to this new table dax query (As i am new to power bi dont know how to do this)
Hey @Maya2988,
Just include that column in SUMMARIZE function:
table1 =
ADDCOLUMNS(
SUMMARIZE(
tabA,
tabA[Company],
tabA[Number]
)
, "GD time", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "GD")
, "UC time", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "UC")
, "duration", CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "GD") - CALCULATE(SELECTEDVALUE(tabA[Date]), tabA[Status] = "UC")
)
Thanks @wini_R . It works perfectly. Last question regarding performance. Creating new table using DAX in power bi will create any performace issue in future.
Yes, that might impact the performance depending on how big the original table is. It is always recommended (whenever possible) to do any tranformations in source system or as close to the source as possible (i.e. in SQL db).
Hi @Maya2988,
One way to achieve this is to create a new table based on the original one to get the expected structure and also set the right format for duration column:
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |