Forum Discussion

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

Gain access to text values for calc column

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

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    10 years ago

    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

8 Replies

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

    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's avatar
        ThomasDay
        Icon for Impactful Individual rankImpactful Individual
        10 years ago

        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