topic Re: Time range for time column in DAX Commands and Tips

Time range for time column

gustavjMC — Thu, 12 Nov 2020 11:04:14 GMT

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?

Re: Time range for time column

amitchandak — Thu, 12 Nov 2020 11:36:33 GMT

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

Re: Time range for time column

gustavjMC — Thu, 12 Nov 2020 12:04:06 GMT

Thanks for replying!

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

Re: Time range for time column

amitchandak — Thu, 12 Nov 2020 13:19:47 GMT

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

Re: Time range for time column

Element115 — Wed, 09 Dec 2020 18:17:52 GMT

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.