need help in DAX query

[Reddyp]

Hi, I have a SQL query but need to change this to DAX, can you please help 
Store SIT => sum(qty) where Location like ‘%-T’

Story: I need to sum qty from Excel1 and I need to search location column where data ends with -T (example W0032-T) in Excel2

[Anonymous]

Hi @Reddyp ,

Could you tell me if your problem has been solved? If it is, kindly Accept a helpful reply as the solution to make the thread closed. More people will benefit from it.

Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[Reddyp]

@Samarth_18 another question, help me with the syntax 

 if date = '2021' then use cost_amount_physical otherwise use Cost_amount_posted

				Desired Result
Product	Date	cost_amount_physical	Cost_amount_posted	Store Cost
Cycle	01-01-2021	$10		$10
Bike	01-01-2021		$22	$22

[Samarth_18]

@Reddyp , Create a column as below:-

Column =
IF (
    YEAR ( 'Table (2)'[Date] ) = 2021
        && 'Table (2)'[cost_amount_physical] <> BLANK (),
    'Table (2)'[cost_amount_physical],
    'Table (2)'[Cost_amount_posted]
)

[Reddyp]

@Samarth_18 @johnt75 
Super, this works 
if date = '2021' then use cost_amount_physical otherwise use Cost_amount_posted

Column =
IF (
    YEAR ( 'Table (2)'[Date] ) = 2021
        && 'Table (2)'[cost_amount_physical] <> BLANK (),
    'Table (2)'[cost_amount_physical],
    'Table (2)'[Cost_amount_posted]
)

 need to add one more thing 
if date = (Sheet 1)'2021' then use (Sheet1)cost_amount_physical otherwise use (Sheet1)Cost_amount_posted and filter by (Sheet2)location = "Acukland"

[Samarth_18 · Accepted solution]

@Reddyp Update it as below:-

Column =
IF (
    YEAR ( 'Sheet1'[Date] ) = 2021
        && 'Sheet1'[cost_amount_physical] <> BLANK () && related('Sheet2'[location]) = "Auckland",
    'Sheet1'[cost_amount_physical],
    'Sheet1'[Cost_amount_posted]
)

[johnt75]

You may get better performance if you create a new column in Power Query which uses Text.End to check whether the string ends in -T or not. You can then use this new column as a filter for your calculations and it isn't having to do a text comparison each time.

[Samarth_18]

Hi @Reddyp ,

You could try like this:-

Store SIT =
CALCULATE (
    SUM ( excel1[qty] ),
    FILTER ( excel2, CONTAINSSTRING ( excel2[Location], "-T" ) )
)

Note- I am considering you have relation between both loaded excel sheet.

BR,

Samarth

[Reddyp]

@Samarth_18 thank you so much, yes I have relation between 2 excel sheets 
i ran this query and the result was -2, i dont understand this (but checking for issue)

is this query "sum" all colums where text ends with "-T" (example: W0019-T, Woo26-T) and dont sum text without "-T" (example: W0094, W0024) ?

[Samarth_18]

@Reddyp Yes it will sum qty column where location contains "-T".

It would be helpful if you could share the sample data in text format with sample data.