Forum Discussion

Kumar_0606's avatar
Kumar_0606
Helper I
1 year ago
Solved

How to create a Parameter to display data dynamically

Hello Experts,

 

I have a date field. I want to display data dynamically so that whenever User selects particular date, data should be filtered accordingly.

I created a Date parameter (transform data-> New Parameter) with some Values and joined (Many to One) with the actual table.

 

Now I am trying to create a calculation 

Flag Current Month=YEAR(Table[Date]) = YEAR(Paramter table[Date])

 Here I'm unable to select the Parmater Date as they are not populating.

 

Am i missing something or is there any other way to create the flags CM, PM, CY, PY etc.

 

Thanks in advance

 

Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi Kumar_0606,

     

    Based on your requirement, the only reliable and supported approach for dynamic filtering like this is by using a Disconnected Date Parameter Table with Dynamic Measures.

    Here the SELECTEDVALUE(ParameterDate[Date]) returns blank because the slicer uses a formatted MonthYear column that maps to multiple dates. This leads to multiple values being selected behind the scenes and SELECTEDVALUE does not return a value in such cases.

    • So please create a seperate MonthSelector table with one row per month like for example 01 Jan 2025, 01 Feb 2025.
    • Use the MonthYear column from this table in a single select slicer and update your DAX measures to use the selected MonthStart date from the MonthSelector table.

    This will make sure accurate dynamic filtering using disconnected tables and give full flexibility to create CM, PM, PY flags in measures.

     

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

12 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Kumar_0606 You need to create calculated fields to flag the current month, previous month, current year, and previous year based on the selected date parameter.

     

    FlagCurrentMonth =
    IF(
    YEAR(Table[Date]) = YEAR(SELECTEDVALUE(ParameterTable[Date])) &&
    MONTH(Table[Date]) = MONTH(SELECTEDVALUE(ParameterTable[Date])),
    1,
    0
    )

     

    FlagPreviousMonth =
    IF(
    YEAR(Table[Date]) = YEAR(SELECTEDVALUE(ParameterTable[Date])) &&
    MONTH(Table[Date]) = MONTH(SELECTEDVALUE(ParameterTable[Date])) - 1,
    1,
    0
    )

     

    FlagCurrentYear =
    IF(
    YEAR(Table[Date]) = YEAR(SELECTEDVALUE(ParameterTable[Date])),
    1,
    0
    )

     

    FlagPreviousYear =
    IF(
    YEAR(Table[Date]) = YEAR(SELECTEDVALUE(ParameterTable[Date])) - 1,
    1,
    0
    )

     

    Make sure that your parameter table is properly linked to your main table. The SELECTEDVALUE function is used to get the value of the parameter selected by the user.

    • Kumar_0606's avatar
      Kumar_0606
      Helper I
      1 year ago

      Hello Gautam,

      Thanks for the reply.

      I created Current Month flag as you suggested and created a column like

      Amount CM= IF ([Flag Current Month]=1 && Table[Version]="New", [Amount]))

      When i pulled this to the table, getting Blank value.

      • v-achippa's avatar
        v-achippa
        Community Support
        1 year ago

        Hi Kumar_0606,

         

        Thank you for reaching out to Microsoft Fabric Community.

         

        Thank you bhanu_gautam for the prompt response.

         

        To filter data dynamically based on a selected date, please follow these steps:

        • Create a Disconnected Parameter Table, go to Modeling --> New Table and enter this:
          ParameterDate = CALENDAR(DATE(2023, 1, 1), TODAY())

        This creates a date table that is not joined to your main data table. Do not create any relationships. Now add this ParameterDate[Date] column to a slicer on the report, so that to select a date.

        • Create the Dynamic Measure for Current Month Amount:

          Amount CM = VAR SelectedDate = SELECTEDVALUE(ParameterDate[Date])

          RETURN

          CALCULATE(

              SUM('Table'[Amount]),

              'Table'[Version] = "New",

              YEAR('Table'[Date]) = YEAR(SelectedDate),

              MONTH('Table'[Date]) = MONTH(SelectedDate)

          )

        This measure will return the total amount for the current month based on the selected date in the slicer.

        Do not use calculated columns or Power Query parameters directly in DAX, because they are static and will not respond to slicer selections, so dynamic filtering will not work.

         

         

        If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

         

        Thanks and regards,

        Anjan Kumar Chippa