topic Weird date filter behaviour in DAX Commands and Tips

Weird date filter behaviour

Lakka00 — Mon, 08 Jul 2024 16:02:20 GMT

I have a table which works out time values.

I am having issues when filtering it based on date, instead of hiding the irrelevant records it shows ::

The column with the time, is using an expression to workout the time in hours minutes and seconds, it uses the lenght column (Duration format, set to show total seconds).

I have attached screenshots to help make it clearer

DAX on Length calc column

The table with the issue.

I am doing the exact same thing on another table and it works perfectly, not sure why this is happening.

Re: Weird date filter behaviour

foodd — Mon, 08 Jul 2024 16:12:11 GMT

Hello Lakka00 , and thank you for sharing a question with the forum. The following is informational. Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you. Proud to be a Super User!

Re: Weird date filter behaviour

Anonymous — Tue, 09 Jul 2024 05:45:50 GMT

Hi Lakka00

In my speculate, there must be some some formatting issue with your [Length] column.

Please try this measure:

Measure = VAR _len = VALUE(SELECTEDVALUE('Table'[Column1])) VAR _hour = INT(_len/3600) VAR _minute = INT(MOD(_len,3600)/60) VAR _second = MOD(_len,60) RETURN FORMAT(_hour,"00")&":"&FORMAT(_minute,"00")&":"&FORMAT(_second,"00")

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.