LASTNONBLANK() syntax

[paivers]

I would like to get the last Facility value from the dataset meeting this filter criteria. I am trying to use LASTNONBLANK() as follows and getting syntax error "Multiple columns cannot be converted to a Scalar Value”. I believe I need to covert the second parameter, a FILTER returning a set of rows, to a column, but I specify the column in the first parameter. Any clues on the right syntax?

=if(([Readmit Denominator Account]=""||[VISIT_TYPE]<>"INPATIENT"),"",
LASTNONBLANK('Charges'[Facility],
FILTER(Charges,(
'Charges'[YH_MRN] = EARLIER('Charges'[YH_MRN]) &&
'Charges'[Facility] <> EARLIER('Charges'[Facility]) &&
'Charges'[Discharge Date] < (EARLIER('Charges'[AdmitDate])) &&
'Charges'[Discharge Date] >= (EARLIER('Charges'[AdmitDate])-30) &&
Charges[VISIT_TYPE] =(EARLIER(Charges[VISIT_TYPE]))
))
))

 

[MD610]

FILTER() returns a table. You need the last arguement of LASTNONBLANK() to return the result of an expression (usually a number).

I would try wrapping your FILTER() statement with a simple CALCULATE(COUNTROWS(Charges), FILTER(Your Current Filter Logic)). Use this for the second arguement in LASTNONBLANK(). Without actually seeing your sample data, I'm not sure if this will get you what you want but I think it should be in pointing you in the right direction.

 

[Jacob Barnett]

Pete,

Mike is undoubtedly right but using a set of test data I've not been able to get anything remotely sensible out of a LASTNONBLANK() formula on a set of sample data I created. The documentation is basically non existent and good examples are thin on the ground.

This thread (DAX: Get sum of values of the last month having data) started by Kasper De Jong contains a discussion of this function however what might be telling about this example and all the examples in Marco and Alberto's new book is that the expression is always based on a separate table. In the versions I came up with, even with the expression contained in CALCULATE() it seemed that the iterative nature of LASTNONBLANK had a strange effect on the filter context.

While I'm keen to see somebody clear this up, I'm still not convinced that it's your answer and as I said previously, I think that the use of EARLIER() and the necessary double iteration are an over complication that this problem doesn't require.

I created this data set to try a few things out. I working on the basis that your requirement is to return the last facility a patient was at where the discharge date was 30 days later than their admission.

The measure I eventually came up with used TOPN to return the top row from a filtered table that was less than the max date for the given filter context where the row satisfied the condition of having 30 days+ between admission and discharge. From that row the VALUES() gives you the facility. Obviously you will need to adapt for your dataset and maybe add a couple of extra conditions to the FILTER() but hopefully this gets you close.

<max(table1[admitdate])&&

=CALCULATE(VALUES(Table1[FAcility]),
                TOPN(1,
                   FILTER(table1, 
                       Table1[AdmitDate]~max(Table1[AdmitDate])&&
                       Table1[DischargeDate]>=Table1[AdmitDate]+30         
                           ),
                     Table1[AdmitDate])
            )

NB, I swapped the less than symbol for a ~ to stop the site cutting off half of my formula!!

Let us know how you get on, I uploaded by test model here: https://docs.google.com/file/d/0Bz5yMU2oooW2YzBzWndVQ0NtdjQ/edit?usp=sharing

Jacob</max(table1[admitdate])&&

 

[paivers]

Thanks Jacob (and Mike for the pointer on LASTNONBLANK(). I think you are right on the simpler approach and the sample data is spot on for the requirement - thanks once more for going to that length. I updated it, just changing the table field names to the actual ones, but oddly I get a syntax error "Calculation error in column 'Charges'[]: A table of multiple values was supplied where a single value was expected". The syntax looks right to me. Once I get that sorted, I don't understand how it knows to limit it to the same patient. YH_MRN is the patient key to maybe I just add 'Charges'[YH_MRN]='Charges'[YH_MRN] to the filter?=CALCULATE(VALUES(Charges[Facility]),
TOPN(1,
FILTER(Charges,
Charges[AdmitDate]<max(Charges[AdmitDate])&&
Charges[Discharge Date]>=Charges[AdmitDate]+30
),
Charges[AdmitDate])
)

 

[Jacob Barnett]

Difficult to help with the syntax - the formula you posted seems to be missing a bit, not sure whether it was cut off by the site! My example was copied straight out of PowerPivot so should be spot on. Maybe try retyping yours from scratch as the intellisense often does silly things when you are substituting tables/columns.

The beauty of this more simplified approach is that you are back to a plain old filter context on your Pivot - for instance where the formula is returning the facility for 'Linda', the FILTER() within the formula produces a table that just consists of Linda's rows so no further manipulation is required!

 

[paivers]

Jacob, It was an editor thing. I'll post a second reply to this with just the code sample and preview it first.

 

[paivers]

=CALCULATE(VALUES(Charges[Facility]),

TOPN(1,

FILTER(Charges,

Charges[AdmitDate]~max(Charges[AdmitDate])&&

Charges[Discharge Date]>=Charges[AdmitDate]+30

),

Charges[AdmitDate])

)
<max(charges[admitdate])&&

</max(charges[admitdate])&&