Forum Discussion

JamesAH's avatar
JamesAH
Regular Visitor
1 year ago
Solved

Dynamic Filtering when using Field Parameters

I pretty sure I have hit a dead end after many hours of research and testing but I'd love it if someone can help me solve this one. My goal is to use a Sankey diagram to show changes in data over tim...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    JamesAH , Try using

    Create a Parameter Table: Ensure you have a parameter table that allows users to select the two dates dynamically.

    Dynamic Column Selection: Use the SELECTEDVALUE function to dynamically reference the columns based on the selected dates.

    Change Flag Calculation: Calculate the change flag dynamically without hardcoding the dates.

    DAX
    ChangeFlag =
    VAR SelectedDate1 = SELECTEDVALUE('ParameterTable'[Date1])
    VAR SelectedDate2 = SELECTEDVALUE('ParameterTable'[Date2])
    VAR SourceStatus = LOOKUPVALUE('InputTable'[Stage], 'InputTable'[ExtractionDate], SelectedDate1)
    VAR CurrentStatus = LOOKUPVALUE('InputTable'[Stage], 'InputTable'[ExtractionDate], SelectedDate2)
    RETURN
    IF(
    SourceStatus <> CurrentStatus,
    1,
    BLANK()
    )

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

JamesAH , Try using

Create a Parameter Table: Ensure you have a parameter table that allows users to select the two dates dynamically.

Dynamic Column Selection: Use the SELECTEDVALUE function to dynamically reference the columns based on the selected dates.

Change Flag Calculation: Calculate the change flag dynamically without hardcoding the dates.

DAX
ChangeFlag =
VAR SelectedDate1 = SELECTEDVALUE('ParameterTable'[Date1])
VAR SelectedDate2 = SELECTEDVALUE('ParameterTable'[Date2])
VAR SourceStatus = LOOKUPVALUE('InputTable'[Stage], 'InputTable'[ExtractionDate], SelectedDate1)
VAR CurrentStatus = LOOKUPVALUE('InputTable'[Stage], 'InputTable'[ExtractionDate], SelectedDate2)
RETURN
IF(
SourceStatus <> CurrentStatus,
1,
BLANK()
)

JamesAH's avatar
JamesAH
Regular Visitor
1 year ago

I just solved this! Worked out that rather than using the pivioted table to to check for change I used the original table and the lookups. So thanks bhanu_gautam as whilst your response wasn't perfect, it gave me the idea.