cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors