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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Use slicer for calculated columns

llo

 

I have created a calculated column showing how many in running calls there are at 15 minute intervals throughout the day. I have a date table with the intervals and another table with the start date/time and end date/time of the calls and caller ids etc. I have manged to add the results to the date table where it shows me how many calls are at each interval.

 

When I try to use Slicers/Filters from the other table, the values created from the calulated column do not change. I noticed with measures that the value changes, but i have tried to replicate the calculated column as a measure,  but it doesn't give the desired results.

 

For the Calculated column I used the following DAX formula

 

In running calls =

 

CALCULATE(COUNTROWS('Calls'),
FILTER('Calls', ([START_DATE] <= ('Date'[DateTime])
&& [End Time]>= ('Date'[DateTime]))))

 

Is there anyway of converting this to a measure? 

 

Thanking you in advance

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

If it is convenient, could you share some data sample and your desired output so that we could help further on it?

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hello Cherry @v-piga-msft

 

I have a table with Date/ Time like below in 15 Minute intervals. 

 

Date/Time

01/01/2016 00:00

01/01/2016 00:15

 

I also have another Table with Start & End Times.

 

      Start Time                   EndTime

01/01/2016 00:00         01/01/2016 01:00

01/01/2016 00:00         01/01/2016 02:00

01/01/2016 00:00         01/01/2016 03:00

01/01/2016 00:00         01/01/2016 03:00

01/01/2016 00:00         01/01/2016 03:00

 

I have already managed to add a column to the date/time table that shows how many times, either the Start Time or End Time fall on that particular time in the date/time table. The result is lets say

 

Date/Time                                    Concurrency

01/01/2016 00:00                               5

01/01/2016 00:15                               5

01/01/2016 00:30                               5

01/01/2016 00:45                               5

01/01/2016 01:00                               5

01/01/2016 01:15                               4

 

This way is fine if i am looking for an overall picture, but i face problems when I try to use slicers/filters. The Concurrency values stay the same. 

 

In my orginal question, I mentioned that measures change when a slicer is used. I would like to have a measure that gives me the same results if possible

 

Thanks

Joe

Greg_Deckler
Super User
Super User

Calculated columns are calculated at the time of data load and never again. You need to use measures which are calculated dynamically.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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