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
shreep1
Helper II
Helper II

Filtering issues Calculated Column versus Custom Column

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. 
 
shreep1_0-1700692356399.png

 

 shreep1_1-1700692435443.png

 

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

 

5 REPLIES 5
OguzMavice
Resolver II
Resolver II

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?

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,

Hello @OguzMavice , 

 

can you help me here as well?

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Just unclick those values. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.