Forum Discussion

D4life's avatar
D4life
Regular Visitor
2 years ago
Solved

Parameter fields display only data for the current month by default

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?

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

     

     

     

     

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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

  • D4life's avatar
    D4life
    Regular Visitor
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

    • D4life's avatar
      D4life
      Regular Visitor
      2 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.