Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Report filter automatic result

Hello experts!   I need to filter the report to recognize the most recent date, then filter to only show results for the group of that date. For example...   The most recent event date on the dat...
v-caliao-msft's avatar
v-caliao-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

Hi Pchapple,

 

According to your description, you need to only show most recent event date with add any filter, right?

 

I have tested it on my local environment, there is no any Minimum and Maximum option for a date value.

So you need to create another table to get only the lastest date for each event, and then use the new created table to create visuals.

Create a column in the original table
LatestDate = CALCULATE(MAX(Latest[DATE]),ALLEXCEPT(Latest,Latest[Event#]))
Then create a new table
LatestEvent = SUMMARIZE(Latest,Latest[Event#])
Add those columns to the new created table.
LatestDate = LOOKUPVALUE(Latest[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat1 = LOOKUPVALUE(Latest[RevCat1],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat2 = LOOKUPVALUE(Latest[RevCat2],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat3 = LOOKUPVALUE(Latest[RevCat3],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat4 = LOOKUPVALUE(Latest[RevCat4],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat5 = LOOKUPVALUE(Latest[RevCat5],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])

 

 

Regards,

Charlie Liao

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Thanks v-caliao-msft

     

    When you are referecning the table Latest... for example...

     

    LatestDate = CALCULATE(MAX(Latest[DATE]),ALLEXCEPT(Latest,Latest[Event#]))

     

    What table are you refering to?  Is it the original table? When I try to create the new column in the original table as you described, its giving me an error.  

     

    Sorry for the newbie questions...

     

    Paul

    • v-caliao-msft's avatar
      v-caliao-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      Hi Pchapple,

       

      Yes, create calculated column in the original table. Please share us the error message, so that we can make further analysis.

       

      Regards,

      Charlie Liao

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Hi v-caliao-msft

        Have created the column as suggested however the process for creating a table seems unclear.  Maybe Im not creating the table rtight, but it does not seem that the formulae works when I use ENTER DATA to create a new table.

         

        You say "Then create a new table
        LatestEvent = SUMMARIZE(Latest,Latest[Event#])" however this does not seem to return any results when pasted into the 1st cell

         

        Thanks