Gain access to text values for calc column

[ThomasDay]

Hello again DAX'ers,

I've spent hours on this--scoured the boards and my books and I'm stumped.  I have done a lot of calculating, and now I have some text fields to work with.

I have a table of alpha values with many rows for each ReportID's.  For any one ReportID, there is only one Row/Alpha Value for Schedule S-2, Row 22, Col1...I can filter on that and prove that.  It is a "Y" or a "N" or a "".

I have a summary table of ReportID's.  I would like to extract the value in the Alpha Value Schedule S-2, Row 22, Col1 for the ReportID and put it in a column in the ReportID summary.

How to I get the AlphaValues into this column?  Here's my latest and seemingly closest try:

Disproportionate = 
//should only be one row for each provider
VAR TEMPCount = 
    CALCULATE(
        COUNTA( HOSP10_2014_ALPHA[AlphaItemTxt(40)] )
//This lets me test for a single value
        ,HOSP10_2014_ALPHA[Wksht(7)Line(5)Col(5)] = "S2000012200100")	
//Unfortunately, the LEFT(xx) below is not a scalar. The "MIN" functions return numbers.
//How do I gain access to the Scalar value?	
VAR	
TEMPVALUE = IF(TEMPCount = 1,LEFT(HOSP10_2014_ALPHA[AlphaItemTxt(40)],1),"")
RETURN
TEMPVALUE

Thank you,

Tom

[ThomasDay]

OK, Summarizing the issue and a new stab at it:

• I am trying to extract the single text value for a Report ID of Schedule S-2 Part 1, Row 22, Col 1 from a Table ALPHA.
• And add a calculated column of these values to a summary table for ReportID's.  
• The tables are related via the the ReportID.

The error I get on this is the IF statement syntax is not correct.  No doubt, but I don't know how to filter and do conditional logic in a calc column.

Disproportionate = 
//The File Relationship filters ReportID--The FILTER is configured to pick the right single value
//The result is one ALPHA value
	FILTER(ALPHATable , ALPHATable[ScheduleRowCol] = "ScheduleRowCol")
//The IF says if one value (it is..use the left most character)
//How do I put it into the Disproportionate variable?
	IF(
		COUNTROWS(VALUES(ALPHATable[ScheduleValue])) = 1,
		VALUES (LEFT(ALPHATable[ScheduleValue],1))

The IF is from definitive DAX page 59 as a pattern.  Anyway, you get the idea...I'm tangled up.

Tom

[ThomasDay]

And here's a picture:

[ThomasDay]

OK, I was reading a post by @konstantinos where he used FIRSTNONBLANK to create a scalar.  It looks like that might get me down to a scalar in a filter on a text column.  So tomorrow that's the tact I'll take and use calc column to give me a row iterator, figure out how to filter in there and use FIRSTNONBLANK to see if I can get to one value.   Tom

[konstantinos]

@ThomasDay

I didn't understand exactly what are you truing to achieve. The FIRSTNONBLANK() returns text as a scalar but needs a measure to define which is the first value, like a date or sales etc. 

If you have more details we can try something.

[ThomasDay]

Ha!  Yes, the explanation was all over the place.  I'm wading into text fields for the first time and don't have my familiar calculate to work with in the usual way.

Anyway, I'm reading the powerpivotpro link you included in the Combine CALCULATE and CONTAINS post (link)--when I get better grounding and try some things from there, I'll be able to describe the issue better and pose a better stab at things.  Thanks!  Tom

[ThomasDay]

@konstantinos--I thought I might beat this thing...it's promising but I'm still caught.  The problem is shown in the pic below.  Summarize selected TEXT values (all are Y/N btw) from one file into a related summary table. "AlltheData" is very big...many to one to the Summary Table via ReptID--and I want the values of the "b" Sched-Row-Col to be written to a column in Summary.  I'm using a calculated column.  I'd be happy writing a new table if that's easier with report ID and "b" values.

Here are the three ways I've tried this am.  They're all variants--filter with an AND, Filter within Filter, FirstNonBlank inside.  The error is the same--multiple cols can't be converted to a scalar.  I use one col for the filter, another has the values.  I changed file and field names to make it easier to read (hopefully)

Disproportionate = 
	FIRSTNONBLANK (FILTER
			(AlltheData , 
			AND(AlltheData[SchedRowCol] = "b",
                SEARCH("Y", AlltheData[Values], 1,0))
            )
        ,1)
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Disproportionate = 
FIRSTNONBLANK (
	FILTER(
		FILTER (AlltheData,SEARCH("Y", AlltheData [Values], 1,0)),
		AlltheData[SchedRowCol] = "b"
		)
        ,1)
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value

Disproportionate = 
	FILTER (
		FILTER(AlltheData,[SchedRowCol] = "b"),
		FIRSTNONBLANK(AlltheData[Values],
			SEARCH("Y", AlltheData[Values], 1,0)
			,1)
		)
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value

Any help would be appreciated.  Tom

[v-qiuyu-msft · Accepted solution]

Hi ThomasDay，

In your scenario, you can use LOOKUPVALUE Function to return the [Value] in AlltheData table when [SchedRowCol] is equal to “b” like below:

Column = LOOKUPVALUE(AlltheData[value],AlltheData[ReptID],summary[ReptID],AlltheData[SchedRowCol],"b")

If you have any question, please feel free to ask.

Best Regards,

Qiuyun Yu

[ThomasDay]

Well, I'll be darn.  Here's the final working calculated col.

Disprop Share = 
   LOOKUPVALUE(
               HOSP10_2014_ALPHA[AlphaItemTxt(40)],
               HOSP10_2014_ALPHA[ReptRecNo],
               HOSP10_2014_RPT[ReptRecNo],
               HOSP10_2014_ALPHA[Wksht(7)Line(5)Col(5)],"S2000012200100")

Thank you, Tom