Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to write DAX to filter only specific rows dynamically even when data is changed in the future.

Hello Guys,

 

I'm trying to filter data to show the result on specific criteria from the below data dynamically even when the data gets updated but the logic should remain same..

 

 

Expected Result:

 

 

I need to use line chart to show "Estimated" values from November 2021 to June 2022.

 

Please let me know.

 

Thanks in advance.

Dee

 

General Comment
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Sorry for my misunderstanding.

    Please try the below.

     

    Estimated measure: =
    VAR newtable =
    FILTER (
    SUMMARIZE ( ALL ( Data ), 'Calendar'[Date], Data[Type] ),
    AND ( Data[Type] <> "Actual", Data[Type] <> "Current" )
    )
    VAR groupbymindate =
    GROUPBY (
    newtable,
    Data[Type],
    "@mindate", MINX ( CURRENTGROUP (), 'Calendar'[Date] )
    )
    VAR filter_table =
    FILTER (
    CALCULATETABLE ( Data, TREATAS ( groupbymindate, Data[Type], Data[Date] ) ),
    Data[Type] = MAX ( Data[Type] )
    && Data[Date] IN VALUES ( 'Calendar'[Date] )
    )
    VAR monthyeartable =
    SUMMARIZE (
    ADDCOLUMNS (
    SUMMARIZE ( Data, 'Calendar'[Month & Year], Data[Type] ),
    "@result",
    CALCULATE (
    IF (
    SELECTEDVALUE ( Data[Type] ) = "Current",
    SUM ( Data[Estimated] ),
    SUMX ( filter_table, Data[Estimated] )
    )
    )
    ),
    'Calendar'[Month & Year],
    [@result]
    )
    RETURN
    IF (
    HASONEVALUE ( 'Calendar'[Month & Year] ),
    SUMX ( monthyeartable, [@result] )
    )

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Create a measure like

     

     

    Calculate(Sum(Table[Estimate]), filter(Table, Table[Date] = minx(filter(allselected(Table), Table[Type] = max(Table[Type])),[Date]) && Table[Type] in {"02-Update", "05-TNP", "06-APP")) + Calculate(Sum(Table[Estimate]), filter(Table, Table[Type] = "01-Current"))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak 

       

      Thank you very much for the response.

       

      I'm getting below error in the DAX calculation.

       

       

      Thanks

      Dee

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi, 

    I tried to follow the concept and created a sample pbix file like attached.

    Please check the below picture and the attached pbix file.

     

     

    Estimated measure: =
    VAR newtable =
    FILTER (
    SUMMARIZE ( ALL ( Data ), 'Calendar'[Date], Data[Type] ),
    AND ( Data[Type] <> "Actual", Data[Type] <> "Current" )
    )
    VAR groupbymindate =
    GROUPBY (
    newtable,
    Data[Type],
    "@mindate", MINX ( CURRENTGROUP (), 'Calendar'[Date] )
    )
    VAR filter_table =
    FILTER (
    CALCULATETABLE ( Data, TREATAS ( groupbymindate, Data[Type], Data[Date] ) ),
    Data[Type] = MAX ( Data[Type] )
    && Data[Date] IN VALUES ( 'Calendar'[Date] )
    )
    RETURN
    IF (
    HASONEVALUE ( 'Calendar'[Month & Year] ),
    IF (
    SELECTEDVALUE ( Data[Type] ) = "Current",
    SUM ( Data[Estimated] ),
    SUMX ( filter_table, Data[Estimated] )
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you very much Jihwan_Kim  that's a great solution.

       

      But the line chart is broken with the points. Please see below

       

      Thanks

      Dee

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Thank you for your feedback.

        May I know how your expected result looks like?