Forum Discussion

ThomasDay's avatar
ThomasDay
Icon for Impactful Individual rankImpactful Individual
10 years ago
Solved

Tagging most recently reported data

Yes, this seems like it should be simple but it's elusive to me.   I have a table of healthcare provider data--thousands of them.  There is one "report demographics record" per report for each data...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    OK, first in "HOSP10_2014_RPT" table, create a custom column with the formula:

    Year = RIGHT([Report Dt],4)*1

    Then, what I did was create another table "Providers" that just lists the providers, Provider 1, Provider 2, Provider 3. You could use a query that just grabs distinct values for "Who" against the same data source you are pulling from right now. Relate the two tables on the "Who" columns.

     

    Create a custom column in this new "Providers" table with the formula:

     

    Max = MAXX(RELATEDTABLE(HOSP10_2014_RPT),HOSP10_2014_RPT[Year])

    Finally, back in "HOSP10_2014_RPT" table, create the following custom column:

    CurrentRept = IF([Year] = RELATED(ProviderTable[Max]),True,False)

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

OK, first in "HOSP10_2014_RPT" table, create a custom column with the formula:

Year = RIGHT([Report Dt],4)*1

Then, what I did was create another table "Providers" that just lists the providers, Provider 1, Provider 2, Provider 3. You could use a query that just grabs distinct values for "Who" against the same data source you are pulling from right now. Relate the two tables on the "Who" columns.

 

Create a custom column in this new "Providers" table with the formula:

 

Max = MAXX(RELATEDTABLE(HOSP10_2014_RPT),HOSP10_2014_RPT[Year])

Finally, back in "HOSP10_2014_RPT" table, create the following custom column:

CurrentRept = IF([Year] = RELATED(ProviderTable[Max]),True,False)

 

  • ThomasDay's avatar
    ThomasDay
    Icon for Impactful Individual rankImpactful Individual
    10 years ago

    Thank you Greg_Deckler.  I like your answer very much--it's an important "pattern" of solution.  

     

    I see this as using "scratch" tables for some of these sorts of calculations.  In this case a provider table of important attributes.  

     

    With only 6K providers, does it matter if I end up making 100 of these indicators like last report date?  I can put all sorts of "cohort" types of indicators in it....bed size quintiles, financial performance quintiles, fips they serve, and so on.  It's surely more complex than that since quintiles will be sharded further with location/size/case complexity forming cohorts--and then quintiles within those.  I guess the learning curve will remain steep for awhile.

     

    As an aside--entering DAX-land from more "traditional" programming...I was imagining I could step through each report row in the table sorted by provider and report date (DESC), and for each row see if there are other rows with the same provider number, then if so--tag the report row in some fashion.  No muss, no fuss.  

    Perhaps there is a way to do that in DAX?  I was trying to figure how COUNTROWS and VALUES could give me an indication of the presence of other rows somehow, then maybe use EARLIER to get back to the report row to tag it.  If the sort were preserved--I'd be able to tag it properly as current, current -1, and so on.

     

    If there are other approaches, I'd love to hear.  In the meantime, I'm going with this.

     

    Thanks again smoupre.  This is a huge help.  Tom