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()
    )

JamesAH's avatar
JamesAH
Regular Visitor
1 year ago

bhanu_gautam  Thanks, but I dont think this works as I don't have stage as a column any more, the stages are contained in the columns with dates after the data is pivoted. Here is a the data, note that over time the number of columns will expand :