DAX QUERY - SUM VLOOKUP

[united2win]

Hi,

I have the below two tables:

Table 1

Concatenate	Time Spent
DE-VAT Return	2
DE-VAT Return	3
FR-VAT Return	7

Table 2:

Concatenate	Count	Sum Time Spent
DE-VAT Return	2	DAX Formula? (5)
FR VAT Return	1	DAX Formula? (7)

What is the DAX formula I need to include in table 2 to add the sum of Time Spent?

[harshnathani]

Hi @united2win ,

Create a Column in Table1.

Column = CALCULATE (Table1 [TimeSpent]), Filter( Table1, Table1[Concatenate] = EARLIER(Table1[Concatenate])))


Then do a lookup in table 2

Lookup value = Lookup (Table1[Column], Table1[Concatenate] , Table2[Concatenate])

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[united2win]

Surely, there must be a simplier way without having two additionals columns?

[united2win · Accepted solution]

Answer can be found here:

https://community.powerbi.com/t5/Desktop/SUMIF-Equivalent-in-DAX/td-p/230727

[amitchandak]

@united2win , assume the text in the first one is "DE-VAT Return 2"

Use split by column and split it by space  into 3 column

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

col1, col2, col3

DE-VAT, Return, 2

Then count(Table[Col1])

time Spend =Sum(Table[Col1])

[united2win]

Hi Amit, 

The values of time spent are in the "Time Spent" column, so no need to split by column (space delimeter). The formatting of the table just doesn't show this.