Forum Discussion

Reddyp's avatar
Reddyp
Icon for Helper I rankHelper I
4 years ago
Solved

need help in DAX query

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

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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]
)

9 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Reddyp
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.

         

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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.

  • Reddyp's avatar
    Reddyp
    Icon for Helper I rankHelper I
    4 years ago

    Samarth_18 another question, help me with the syntax 

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

     




        Desired Result
    ProductDatecost_amount_physicalCost_amount_postedStore Cost
    Cycle01-01-2021$10 $10
    Bike01-01-2021 $22$22
    • Samarth_18's avatar
      Samarth_18
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
        Reddyp
        Icon for Helper I rankHelper I
        4 years ago

        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"

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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