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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Saxon202202
Helper III
Helper III

Measure not display in chart

I have the following two columns are in direct query Date&Time and Time2. I created new measue based on the Date&time column for Time1. 

Time1 = FORMAT(CALCULATE(MAX('TIME'[Date&Time])),"HH:MM:SS")

I created the Time difference measure based on the Time1 and Time2 measures by usig the following measure.

Time Difference = FORMAT(SELECTEDVALUE('TIME'[Time2],0)-[Time1],"HH:MM:SS")

I am trying to dragging the "The Time Difference" measure into Y axis (Stacked column chart) but unale to do it and it went to tooltip option. 
I want to see the time difference (Max, Min, Count and Average) based on the Date&Time column(X-axis) in Stacked column chart.
Can you please assite me where the measure need to be modify in order to move the measue column into Y axis (Stacked column chart).

Saxon202202_0-1673990355370.pngSaxon202202_1-1673990404109.png


Actual result in Excel for Max. 

Saxon202202_2-1673990458952.png
Data:

Date&TimeTime2
12/12/2022 18:21:2520:47:44
12/12/2022 18:22:0820:44:49
12/12/2022 18:22:0820:44:49
08/12/2022 23:08:2209:05:39
08/12/2022 18:21:2520:47:44
06/12/2022 05:38:0809:05:37
05/12/2022 05:36:3916:56:45
04/12/2022 10:29:3820:28:56
03/12/2022 10:29:4920:39:30
02/12/2022 05:27:2816:29:52
01/12/2022 10:31:24 
01/12/2022 10:31:24 
01/12/2022 10:31:24 
01/12/2022 10:31:24 
01/12/2022 10:31:24 



 

6 REPLIES 6
MFelix
Super User
Super User

Hi @Saxon202202,

 

Since you are formatting the measures has text you cannot used them in a bar chart or any ohhter type except tables. 

 

 

To have this working on a chart you need to use the formatting trough a calculation item. 

 

If you want I can setup an example and share with you. 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix , Thanks for your reply. Yes, please help me and explain the steps and share the PBI file. 
just let you know the source of data is direct query and came SQL.
I am trying to change the datatype by using convert function with data type "Date and Time" but still same result.

Hi @Saxon202202 ,

 

Unfortunalty the calculation groups do not work in Direct Query mode so this option is not available for your case, even so if you are interested I can send you a wakltrough.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix , Thanks for your reply again. Yes, I am interested can you please share the information step by step so it will help to understand. 
Question: Direct query will be support to create a new calculated column?

v-shex-msft
Community Support
Community Support

HI @Saxon202202,

I think this should be related to format function.
AFAIK, format function will force convert the result value to text which not able to be aggregated. (notice: measure formula not able to use on axis field and text type not able to use as aggregated value field)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft ,
Thanks for your reply.
Is there alternative option I can achieve the desired result? Please 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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