Parameters and slicers

[dabenchii]

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.

[parry2k]

@dabenchii you need to use dynamic M query parameters, read all about it here. Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

[MFelix]

Hi @dabenchii ,

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?

[dabenchii]

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 · Accepted solution]

Hi @dabenchii ,

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.

[dabenchii]

I am indeed using Import connection mode, and this works perfectly. Thank you! Just have one question. For the DimStatus table, is there a workaround for it to be in the same table as the data table and not in a separate table?

[Anonymous]

Hi @dabenchii ,

I think it's better for you to create a new separate table for DimStatus. If you use the column from data table, the result will be impacted by row context.

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.