Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Parameters and slicers

Hi, is there a way for a parameters to be visually represented as a slicer in the report? I have two date parameters named StartDate and EndDate. I used them to group productname under active or inactive. Now I want to put the date parameters into a date slicer so I don't have to open Power Query every time I want to change the parameters' values. Is that possible?
I know there is the measure approach for the active and inactive but I want to group the productname under active/inactive in rows like this. 

Product one prodkey 2 has roweff date of 10/1/2024 to 10/5/2024 while product one prodkey 1 has roweff date of 1/1/2024 to 9/30/2024 and product two prodkey 3 has roweff date of 10/6/2024 to current date. The parameters values are set to: StartDate 10/1/2024 and EndDate 10/5/2024. That's why product one prodkey 2 is active. Here is why I want to visualize the parameters as a date slicer for the matrix to be dynamic with the parameters.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    As far as I know, dynamic M query parameters only supports to Direct Query connection mode. So if you use Direct Query connection to connect to your data source it is a good choice.

    If you use import connection mode, here I suggest you to try measure by DAX.

    Firstly, we need to create an unrelated Dimstatus table and an unrelated Calendar table to help calculation.

    DimStatus = 
DATATABLE(
    "Status",STRING,
    "Order",INTEGER,
    {
    {"Active",1},
    {"Inactive",2}
    }
)
    Calendar = CALENDARAUTO()

    Measure:

    MEASURE = 
VAR _RangeStart =
    MIN ( 'Calendar'[Date] )
VAR _RangeEnd =
    MAX ( 'Calendar'[Date] )
VAR _Roweffstart =
    MAX ( 'Table'[roweffstart] )
VAR _Roweffend =
    MAX ( 'Table'[roweffend] )
VAR _CurrentStatus =
    IF (
        AND (
            _Roweffstart <= _RangeEnd,
            OR ( _Roweffend >= _RangeStart, _Roweffend = BLANK () )
        ),
        "Active",
        "Inactive"
    )
VAR _result =
    IF ( _CurrentStatus = MAX ( DimStatus[Status] ), 1, 0 )
RETURN
    _result

    Add this measure into value field and set it to show items when value =1. Result is as below.

     

    Best Regards,
    Rico Zhou

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

     

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous ,

     

    When you refer that you have the parameter on the power query do you mean that a column is calculated based on those values or is a way to filter out information from the source data?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Yes, a column is calculated based on those values. I added a custom column for the product query that uses two parameters named StartDate and EndDate. I want to ask is if there is a way to make the StartDate and EndDate parameters a slicer inside the report just like a regular date slicer.
      This is my code for the custom column
      = Table.AddColumn(#"Changed Type", "Custom", each let
      Source = #"Changed Type",
      AddStatus = Table.AddColumn(Source, "Status", each
      if [roweffstart] <= EndDate and ( [roweffend] >= StartDate or [roweffend] = null )
      then "Active"
      else "Inactive"
      )
      in
      AddStatus)
      And these are the parameters

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous ,

         

        As far as I know, dynamic M query parameters only supports to Direct Query connection mode. So if you use Direct Query connection to connect to your data source it is a good choice.

        If you use import connection mode, here I suggest you to try measure by DAX.

        Firstly, we need to create an unrelated Dimstatus table and an unrelated Calendar table to help calculation.

        DimStatus = 
DATATABLE(
    "Status",STRING,
    "Order",INTEGER,
    {
    {"Active",1},
    {"Inactive",2}
    }
)
        Calendar = CALENDARAUTO()

        Measure:

        MEASURE = 
VAR _RangeStart =
    MIN ( 'Calendar'[Date] )
VAR _RangeEnd =
    MAX ( 'Calendar'[Date] )
VAR _Roweffstart =
    MAX ( 'Table'[roweffstart] )
VAR _Roweffend =
    MAX ( 'Table'[roweffend] )
VAR _CurrentStatus =
    IF (
        AND (
            _Roweffstart <= _RangeEnd,
            OR ( _Roweffend >= _RangeStart, _Roweffend = BLANK () )
        ),
        "Active",
        "Inactive"
    )
VAR _result =
    IF ( _CurrentStatus = MAX ( DimStatus[Status] ), 1, 0 )
RETURN
    _result

        Add this measure into value field and set it to show items when value =1. Result is as below.

         

        Best Regards,
        Rico Zhou

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