Creating Child Field

[ThomasDay]

Hello Fellow Dax'ers,

I've been spinning on this one and it seems silly so I thought I'd catch my breath and ask for a bit of help.

I have a file which has a bunch of records:  They look like this in a Matrix--that's three records.  When I'm in the top row's record, I want to retrieve the ReptRecNo of the next ranking row and put it in a column.  

I know that the same Provdrno(50002) and the next RankRow..(This row plus 1) will find that single record or nothing.  That's it.  How do I retrieve that as a column definition?

PriorReptRecNo = 
		VAR RankOfNextRow = HOSP10_2014_RPT[RankReportFYEnds] +1
		VAR SameProvdrNo = HOSP10_2014_RPT[ProvdrNo]
		RETURN
		Calculate (VALUES(HOSP10_2014_RPT[ReptRecNo]),HOSP10_2014_RPT[RankReportFYEnds] = RankOfNextRow, 
			HOSP10_2014_RPT[ProvdrNo] = SameProvdrNo)

The column is blank...it's also blank with MIN instead of Values.

Any thoughts?  Thanks in advance...Tom

[Vvelarde]

@ThomasDay

hi:

PriorReptRecNo =
VAR RankOfNextRow =
    VALUES ( HOSP10_2014_RPT[RankReportFYEnds] ) + 1
VAR SameProvdrNo =
    VALUES ( HOSP10_2014_RPT[PROVDRNO] )
RETURN
    CALCULATE (
        VALUES ( HOSP10_2014_RPT[ReptRecNo] ),
        FILTER (
            ALL ( HOSP10_2014_RPT ),
            HOSP10_2014_RPT[RankReportFYEnds] = RankOfNextRow
                && HOSP10_2014_RPT[ProvdrNo] = SameProvdrNo
        )
    )

[ThomasDay]

@Vvelarde  Thanks very much.  

I get a "A table of multiple values was supplied where a single value was expected." so I'll have to see where my description went awry and add "filters" to get to one value.  

PriorReptRecNo = 
	VAR RankOfNextRow = VALUES ( HOSP10_2014_RPT[RankReportFYEnds] ) + 1
	VAR SameProvdrNo = VALUES ( HOSP10_2014_RPT[PROVDRNO] )
	RETURN
		CALCULATE (
			VALUES ( HOSP10_2014_RPT[ReptRecNo] ),
			FILTER (
				ALL ( HOSP10_2014_RPT ),
				HOSP10_2014_RPT[Hosp Type] = "  Genl_Short Term" &&
				HOSP10_2014_RPT[RankReportFYEnds] = RankOfNextRow
                && HOSP10_2014_RPT[ProvdrNo] = SameProvdrNo
			)
		)

You can see I added one additional filter already for  "  Genl_Short Term" but couldn't clear the error right off...so I'll tease apart and see what the components yield in the am.  If anything hits you between now and then, let me know.  Thanks again, Tom

[v-sihou-msft · Accepted solution]

@ThomasDay

In your formula, the issue is on  "HOSP10_2014_RPT[Hosp Type] =  VALUES ( HOSP10_2014_RPT[RankReportFYEnds] ) + 1". The VALUES() function returns a column of data, you can't have compare current row context with a column of values. Just remove the Values() in your variable, it should work. 

PriorReptRecNo = 
VAR RankOfNextRow =
    Table3[RankReportFYEnds] + 1
VAR SameProvdrNo =
    Table3[PRODVRNO]
RETURN
    CALCULATE (
        VALUES ( Table3[ReptRecNo] ),
        FILTER (
            ALL ( Table3 ),
            Table3[RankReportFYEnds] = RankOfNextRow
                && Table3[PRODVRNO] = SameProvdrNo
        )
    )

You can also try another way  by using LOOKUPVALUE. 

Lookup = 
LOOKUPVALUE(Table3[ReptRecNo],Table3[RankReportFYEnds],Table3[RankReportFYEnds]+1,Table3[PRODVRNO],Table3[PRODVRNO])

Both formulas work properly.

Regards,