Filtering issues Calculated Column versus Custom Column

[shreep1]

Hello Microsoft PowerBI community,

Hello 

@Amit, @Greg , @tamerj1 , @lbendlin 

@ HenriqueReis

 @  d_gosbell

@amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler 

I am trying to eliminate the 11th hour or the time period between 11:00 am- 11:59 am. My X-axis is the Hour Clock where:

Hour Clock is the Calculated Column called Hour Custom where

Hour Custom = HOUR('T_Cycle_Takt_Time_Detail'[Timestamp])

So, I went under Timestamp column(Data type-time) which is a Custom Column inside of a Query Editor and tried filtering out all the values between 11:00 am to 11:59 am. But I still was not able to eliminate or filter out the 11:00 clock. 

 

Can you please suggest any ideas here to fix this issue?

Thanks

[OguzMavice]

Hi @shreep1 , I think so it might be related 2 different causes;

one of them is relationship of your model. Could you please check data in X axes in your graphs and data in your slicer? What are the source tables of these data and if they are came from different table ( like fact and master date tables) what is the relationship between these two tables?

the other is;

the result of this dax code;  Hour Custom = HOUR('T_Cycle_Takt_Time_Detail'[Timestamp]) 

should be returned as "whole number". like 12,13,14,15

So I expected to see these data in your slicer in order to filter out data. Could you please check types of data after checking their usage and tables' relationship?

[shreep1]

Hello 

@Amit, @Greg , @tamerj1 , @lbendlin 

@ HenriqueReis

 @  d_gosbell

@amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler 

I only have 1 data table inside this whole report called T_Cycle_Takt_Time_Detail

My Timestamp variable inside of a 'T_Cycle_Takt_Time' data table is in Time Format and Hour Custom calculated column is in Whole Number Format. 

Neither unchecking the Timestamp variable inside of a Query Editor helps nor converting the Timestamp into Whole Number helps because it totally messes up the Time as it yields either 0 or 1 if I changed its data type. 

So, looks like I need to write a DAX code here to address this issue and eliminate the 11th hour. 

I tried writing Calculated Column something like this:

Hour Custom 11th hour Exclude = CALCULATE(HOUR('T_Cycle_Takt_Time_Detail'[Timestamp]),'T_Cycle_Takt_Time_Detail'[Timestamp]<>11))

But this shows error: 

A single value for column 'Timestamp' in table 'T_Cycle_Takt_Time_Detail' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result. 

Can you please help me here?

Thanks,

[shreep1]

Hello @OguzMavice , 

can you help me here as well?

[parry2k]

@shreep1 can you share what you used to filter in the PQ?

[shreep1]

Just unclick those values.