Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX help on filter context

Hi Team,   Please can you help me on below requirement.   I have Table A with below fields Name    Date                    Amount A          2022/11/23          10 A          2022/12/20       ...
DAX
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    According to your description ,  you want to “CASE WHEN (DATE1 >= '2022-11-21 00:00' AND DATE2  < '2022-11-22 00:00' ) THEN AMOUNT”.

    Here are the steps you can refer to:

    (1)My test data is the same as yours.

    (2)For your need , i recommend you to create a date table as slicer instead of using the date column in your table. You can click “New Table” and enter this:

     

    Date = CALENDAR( FIRSTDATE('Table'[Date1]),LASTDATE('Table'[Date1]))

     

    And we do not need to create relationship between two tables.

    (3)We can click “New Measure”  to create a measure:

     

    Measure = var  _min = MIN('Date'[Date])

var _max = MAX('Date'[Date])

var _t =FILTER('Table' , 'Table'[Date1]>= _min && 'Table'[Date1] <_max)

return

SUMX(_t,[Amount])

     

    (4)Then we can put the ’Date‘[Date] on the sliver visual and the [Name] and the measure  on the visual and we will meet your need :

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

AilleryO's avatar
AilleryO
Icon for Memorable Member rankMemorable Member
3 years ago

Hi,

 

First I think you should create 2 variables for your dates.

And to "grab" the value from your slicers, could you use MIN and MAX ? It should return what you expect otheriwse give us more details on your project.

Then compare :

VarDate1 >= DATE( 2022 , 11 , 21 ) && VarDate2 < DATE( 2022, 1, 22 )

For dates you should use date function, and for the AND you can use &&, or when you need OR ||.

 

Let us know first if it works with MIN and MAX before getting into the details of IF test, if needed 😉

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi AilleryO Thanks for your reply. Actually Date 1 and Date 2 fields are from two different tables. Table B is created from Table A like Table B = DISTINCT('Table A'[Date 1])

     

    Sample data:

    NameDate 1             Amount

    A23/11/2021    10
    A20/12/2021    20
    A23/11/2022    30
    B15/10/2021    10
    B15/10/2022     70

     

     

    Requirement is between Date 1 and Date 2 range what is the total amount by name.

     

    I have created variables like below . But how to write return statement like 

    if(Table A[Date1]>= Date1 &&Table B[Date2]< Date2,Amount,0)  -- It''s throwing error