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
gustavjMC
Frequent Visitor

Time range for time column

Hello!

 

I'm working on a report where i want my graph to be dependent on a start and stop time from another table. 

 

In SQL my code would look something like this:

SELECT [Value]

            ,[logTime]

FROM database

WHERE logTime BETWEEN [startTime] AND [stopTime]

 

My code in dax for creating a calculated column looks like this (or at least, this is what I want to achieve)

Range = FILTER('Temp'[logTime], 'Temp'[logTime] > 'Wash'[startTime] AND 'Temp'[logTime] < 'Wash'[logTime])
 
Is there any possibility of doing this kind of calculated column where the start and stop time of the column is based on another table?
 
1 ACCEPTED SOLUTION

Thanks for replying!

With this COUNTX function, it only counts the rows, but i want the actual rows in a table to be returned 🙂

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@gustavjMC ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

amitchandak
Super User
Super User

@gustavjMC , This should have worked as a column in 'Temp' table

 

Range = Countx(FILTER('Temp', 'Temp'[logTime] > 'Wash'[startTime] AND 'Temp'[logTime] < 'Wash'[logTime]), 'Temp'[logTime])

 

refer video for example -https://www.youtube.com/watch?v=czNHt7UXIe8

Thanks for replying!

With this COUNTX function, it only counts the rows, but i want the actual rows in a table to be returned 🙂

if by ' but i want the actual rows in a table to be returned' you actually mean that you want the total count of rows from the table that is returned after applying the FILTER(), then, if I am not mistaken, you need to effect a filter transition programmatically, and the only way to do this in DAX is not the FILTER() function, but CALCULATE and CALCULATETABLE.

 

Rather than copy/paste a whole chapter, read chapter 5 from the book: 'The Definitive Guide to DAX,' 2nd edition,  by Marco Russo and Alberto Ferrari.  It's all explained there better than I could.

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.

Top Solution Authors