How to use CALCULATE with Text data

[tdeshaye]

Hello All

I'm sure there is a simple solution to what I'm trying to do, and I have not been able to figure it out.

I have a CATALOG table:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]product (KEY)[/TD]
[TD]Attribute1[/TD]
[TD]Attribute2[/TD]
[/TR]
[TR]
[TD]product1[/TD]
[TD]a[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]product2[/TD]
[TD]a[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]productN[/TD]
[TD]b[/TD]
[TD]e[/TD]
[/TR]
</tbody>[/TABLE]


I have another table with DATA, linked to the CATALOG table via the PRODUCT Key

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]product[/TD]
[TD]date[/TD]
[TD]data2[/TD]
[TD]data3[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]LastWeek[/TD]
[TD]A[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]LastYear[/TD]
[TD]B[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]LastWeek[/TD]
[TD]A[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]LastYear[/TD]
[TD]A[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


If I want to bring into my CATALOG table the information from my DATA table taken for date = LastWeek, I can easily do so for the data3 column, which is numerical, via a CALCULATE(SUM()).

However, if I want to do the same for data2, which has text value in it, I'm stuck.

Ideally, my CATALOG table would end up being:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]PRODUCT(KEY)[/TD]
[TD]Attribute1[/TD]
[TD]Attribute2[/TD]
[TD]data2[/TD]
[TD]data3[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]a[/TD]
[TD]e[/TD]
[TD]A[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]a[/TD]
[TD]g[/TD]
[TD]A[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

is there a simple way to bring back this text information into the CATALOG table (i've tried to convert my text data into a numeric value via additional translation tables but it add a lot of complexity to the model and feels very clunky)

thanks a lot for your help

thomas

 

[svendiamond]

Assuming your "DATA" table (the second table in your post) is on Sheet2, you could try something like this, in cell D2 (and fill down) on your CATALOG table:

=INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))

 

[tdeshaye]

thanks for your reply, but sorry I should have make it clearer that the tables I'm mentioning are PowerPivot table so I'm looking for a PowerPivot & DAX solution.

thomas

 

[Ozeroth]

Hi there,

The simplest expression I can think of is:

= CALCULATE ( VALUES ( DATA[data2] ), DATA[date] = "LastWeek" )

However, since you are accessing a column from the DATA table which is on the many-side of the relationship, there is a chance you would get an error when multiple values of DATA[data2] are returned, so I would probably use IFERROR or HASONEVALUE to handle those cases (though that may never happen with your data).

These versions return blank in the case of multiple values of DATA[data2], but you could change to return something else:

=
CALCULATE (
    IF ( HASONEVALUE ( DATA[data2] ), VALUES ( DATA[data2] ) ),
    DATA[date] = "LastWeek"
)

=
IFERROR (
    CALCULATE ( VALUES ( DATA[data2] ), DATA[date] = "LastWeek" ),
    BLANK ()
)

 

[tdeshaye]

thanks, I was looking for something like taht indeed. But I get the same error message for the two formulas: " The expression contains multiple columns, but only a single column can be used in a true/False expression that is used as a table filter expression"

any idea how I could tweak the formula to avoid this message?

tahnks a lot

thomas

 

[Ozeroth]

That's odd - these both worked fine in a dummy model I set up using your tables from the original post.

Oh, if "LastWeek" is not a literal value but some sort of expression then that might cause this error and the formulas would need to be written differently.
Can you post the exact formulas you used and a sample file if possible?

 

[tdeshaye]

Hi

here's a link to a dummy file that replicates my isse: https://www.dropbox.com/s/8zwe0uyy5hb5e60/test1.xlsx?dl=0

In the PowerPivot table CATALOG, i tried to bring in the Data2 column from the DATA table using one of your formula but it gives me the error message quoted above.

thanks a lot for your help!

thomas

 

[Ozeroth]

Hi again Thomas,

The condition at the end of your original calculated column needs fixing:

[B]Original 'CATALOG'[Data2]
[/B]=
CALCULATE(if(HASONEVALUE(DATA[Data2]),VALUES(DATA[Data2])),DATA[Week]='CATALOG'[RUNDATE]='CATALOG'[Product])

Here is a fix which I think does what you want (without changing any relationships):

[B]Fixed 'CATALOG'[Data2]
[/B]=
CALCULATE (
    IF ( HASONEVALUE ( DATA[Data2] ), VALUES ( DATA[Data2] ) ),
    FILTER ( VALUES ( DATA[Week] ), DATA[Week] = 'CATALOG'[RUNDATE] )
)

Something to look at later: I see there aren't any date relationships in your model. Setting up some appropriate relationships might avoid having to use FILTER to simulate the relationships.

Ozeroth

 

[tdeshaye]

Ho Ozeroth sorry just realized I had forgotten to thank you for your solution which worked perfectly for me!

thomas

Hi again Thomas,

The condition at the end of your original calculated column needs fixing:

[B]Original 'CATALOG'[Data2]
[/B]=
CALCULATE(if(HASONEVALUE(DATA[Data2]),VALUES(DATA[Data2])),DATA[Week]='CATALOG'[RUNDATE]='CATALOG'[Product])

Here is a fix which I think does what you want (without changing any relationships):

[B]Fixed 'CATALOG'[Data2]
[/B]=
CALCULATE (
    IF ( HASONEVALUE ( DATA[Data2] ), VALUES ( DATA[Data2] ) ),
    FILTER ( VALUES ( DATA[Week] ), DATA[Week] = 'CATALOG'[RUNDATE] )
)

Something to look at later: I see there aren't any date relationships in your model. Setting up some appropriate relationships might avoid having to use FILTER to simulate the relationships.

Ozeroth