topic Re: Parameter fields display only data for the current month by default in DAX Commands and Tips

Parameter fields display only data for the current month by default

D4life — Fri, 02 Aug 2024 22:47:22 GMT

I created a table with parameter fields. There are about 30 different columns in the parameter filed. I want the table to only should records for only the current month. Then when the date slicer is used it should show based on the date slicer. If the report is loaded again, it should show only records for the current month.

I thought I could filter the date column withing the Parameter field using dax but this is not working.

Can anyone help?

Re: Parameter fields display only data for the current month by default

lbendlin — Sat, 03 Aug 2024 13:05:35 GMT

Here's a general approach for that:
- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app

Re: Parameter fields display only data for the current month by default

D4life — Sat, 03 Aug 2024 21:33:01 GMT

Thank you for your response.

I created a new column and used dax to check if the "Effective Date" column is a date in the current month, and it will give "Letest" otherwise "Old".
Step 2 - You said I should sort the new column by the original Date column. This gives the following error
" We can't sort "IsCurrentMonth" column by "Effective Date " . There can't be more than one value in "Effective Date " for the same value in "IsCurrentMonth". Please choose a different column for sorting. "

Please assist

Re: Parameter fields display only data for the current month by default

Anonymous — Fri, 09 Aug 2024 09:17:22 GMT

Hi D4life

Thank you very much lbendlin for your prompt reply, please allow me to share some content here.

Here's some dummy data

"Table"

Create a New Date Table. And create a relationship based on date columns.

Date = CALENDAR("7/1/2024", TODAY())

Create a measure. Determines whether to select a date for filtering. By default, the parameter field only shows data for the current month.

Measure Parameter = IF( ISFILTERED('Date'[Date]), CALCULATE( SELECTEDVALUE('Table'[Parameter]), FILTER( ALL('Table'), 'Table'[Date] = SELECTEDVALUE('Date'[Date]) ) ), IF( SELECTEDVALUE('Table'[Date]) = TODAY(), SELECTEDVALUE('Table'[Parameter]) ) )

Here is the result.

Regards,

Nono Chen

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

Re: Parameter fields display only data for the current month by default

D4life — Mon, 12 Aug 2024 13:47:54 GMT

Thank you for your response. In my case the date column has repeated dates. Just like a normal transaction data would have. I noticed your solution had both columns unique. In my case both are not Unique. At least the date column is not unique.
Please assist with any suggestion

Re: Parameter fields display only data for the current month by default

Anonymous — Tue, 13 Aug 2024 02:11:09 GMT

Hi D4life

I understand your needs, please try the following ways:

“Table”

Delete the relationship between two tables.

Create a measure. If the date matches the selection in the slicer, it is marked as 1, otherwise 0. If slicer is not selected, mark the day date as 1.

Measure Parameter = VAR _SelectDate = VALUES('Date'[Date]) RETURN IF( ISFILTERED('Date'[Date]) && SELECTEDVALUE('Table'[Date]) in _SelectDate, 1, IF( NOT(ISFILTERED('Date'[Date])) && SELECTEDVALUE('Table'[Date]) = TODAY(), 1, 0 ) )

Apply this measure to the filters pane.

Create a table visual.

Here is the result.

Regards,

Nono Chen

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