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 inac...
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.

     

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.

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    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.