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..     Expec...
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] )
    )

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?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Jihwan_Kim ,

         

        My expected result should be like below.

         

         

        Thanks in advance

        Dee