Forum Discussion

lordneeko's avatar
lordneeko
Icon for Advocate III rankAdvocate III
5 years ago
Solved

Filter by Latest data for each [Field]

I've seen a few questions and solutions about this, but none of them have addressed the particulars of what I'm trying to do.


I have multiple entities reporting data from many different locations about "Items" and their "Quantities"
I have a page where I'm displaying the details of those reports, but I want it to show the "Latest report" from each location for a particular reported item on a drillthrough page.
 
I expected to be able to use a [Date Reported] Top N filter by Value [Last Location] (BASE in the screenshot)
Like this

 

But that doesn't seem to be working.
The correct data would have each location which had submitted data, and their latest reported numbers.
 
Help?

The data looks something like this (Note there could be hundreds of locations listed, but it will all be the same Item A)
Date ReportedLocationItemQuantity
11/30/2020Location AItem A15
12/1/2020Location BItem A5
10/14/2020Location AItem A13
9/12/2020Location BItem A10


The resulting data should look something like this
Date ReportedLocationItemQuantity
11/30/2020Location AItem A15
12/1/2020Location BItem A5

 

Ultimately, I'd like it to be a "visual Filter" so that when I drillthrough this page it shows all the latest quantity data for that "Item A" on  one visual, but on another visual on the same page I intend to filter it differently to show the "Latest data on Item A by Serial Number" so that it would show data about the last time a particular serial number was reported.

Need Help
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, lordneeko 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

    Table1:

     

    You may create two measures as below.

    Date = 
CALCULATE(
    MAX(Table1[Date Reported]),
    ALLEXCEPT(Table1,Table1[Item],Table1[Location],Table1[Serial Number])
)
    qty = 
var d=[Date]
return
CALCULATE(
    MAX(Table1[Quantity]),
    FILTER(
        ALLEXCEPT(Table1,Table1[Item],Table1[Location],Table1[Serial Number]),
        [Date Reported]=d
    )
)

     

    Then you may create the drillthrough target page like below.

     

    Result:

    Drillthrough 'Location A'

     

    Drillthrough 'Serial Number 2'

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    lordneeko 

    you can create two measures

    Date = CALCULATE(max('Table'[Date Reported]),ALLEXCEPT('Table','Table'[Location]))

qty = MAXX(FILTER('Table','Table'[Date Reported]=max('Table'[Date Reported])&&'Table'[Location]=max('Table'[Location])),'Table'[Quantity])

    • lordneeko's avatar
      lordneeko
      Icon for Advocate III rankAdvocate III
      5 years ago

      Thank you, I accept that creating measures is an option, but I'm looking for a built-in filtering solution. Clearly looking at the terminology within the editor shows that I should be able to filter Latest Date by a category, but I don't appear to be smart enough to figure out how to set it up. I'm trying to avoid setting up different measures for each and every visual on my page to create filters which should be an inherent capability of the filtering features.
      I will, however, take your input as a suggestion, thank you.

  • lordneeko's avatar
    lordneeko
    Icon for Advocate III rankAdvocate III
    5 years ago

    Thank you for the suggestions. Unfortunately, they were not working quite right (mainly because I had to simplify my data due to security reasons) but let's just say I had a lot more columns in my table that just "qty" and the DAX you provided wasn't quite filtering my data correctly.

    So what I'm doing (which appears to be working correctly) is create a separate Query, which references the original query, that then uses a custom column (created based on the fields that I want to have the latest about) that grabs that particular line item and puts a 1 in the column if it is the latest date.
    It looks something like this

    let
    Source = #"Discover Data",
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Date Created] = List.Max(let latestData = [Location] in Table.SelectRows(Source, each [Location] = latestData)[Date Created])
then 1
else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{ "Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Number Found", "Number of Findings", "TimeToIdentify_hours"})
in
    #"Replaced Value"


    I'm following the same structure for my other data table that needs to show the latest "Part number, serial number" for each location

    Seems to be working correctly on my visuals.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, lordneeko 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

    Table1:

     

    You may create two measures as below.

    Date = 
CALCULATE(
    MAX(Table1[Date Reported]),
    ALLEXCEPT(Table1,Table1[Item],Table1[Location],Table1[Serial Number])
)
    qty = 
var d=[Date]
return
CALCULATE(
    MAX(Table1[Quantity]),
    FILTER(
        ALLEXCEPT(Table1,Table1[Item],Table1[Location],Table1[Serial Number]),
        [Date Reported]=d
    )
)

     

    Then you may create the drillthrough target page like below.

     

    Result:

    Drillthrough 'Location A'

     

    Drillthrough 'Serial Number 2'

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • lordneeko's avatar
      lordneeko
      Icon for Advocate III rankAdvocate III
      5 years ago

      That isn't the direction I went, but if I end up refactoring then I'll probably come back to this and go in that direction. Thank you.