Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help me Compare measures

Hi team,

I have a requirement in DAX.

I have two tables like below-

Table 1

Date      D1D2D3D4       M1
June 1,2020      1101110111101       50
June 1,2020      2102110211102       51
June 1,2020      3103110311103       102
June 2,2020      1101110111101       51
June 2,2020      2102110211102       50

Table 2

Date      D1D2D3     M2
June, 2020      11011101    100
June, 2020      21021102    150
June, 2020      31031103     90



I need to create a measure like below-

Count(D4) whose sum(M1) by MTD from Table1>=M2 from Table2 ,for same values of D1,D2,D3,D4 combination in both tables.

For example,

When I select June 1st, for D1 = 1, D2 = 101 and D3 = 1101 combination, M1 = 50 from Table 1 which is less than M2 for the same combination of D1, D2, D3 from Table 2.So D4 in this case is not considered in count.

When I select June 2nd,

for D1 = 1, D2 = 101 and D3 = 1101 combination, M1 = 50 on June 1st and M1 = 51 on June 2nd.So MTD sum of M1 is 101 from Table 1 which is greater than M2 for the same combination of D1, D2, D3 from Table 2.So D4 in this case is considered in count.

 

Any pointers how this can be achieved.Please suggest.

 

Thanks,

Sam

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 

     

    Try the following:


    1. Create a Running total Column :

     

    running total = CALCULATE(SUM('Table'[M1]),FILTER(ALLEXCEPT('Table','Table'[D1]),[Date]<=EARLIER('Table'[Date])))

     

    2. Then create the count measure: 

     

    Measure = CALCULATE(COUNT('Table'[D4]),FILTER('Table',[running total]>=RELATED('Table (2)'[M2])))

     

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

     

    Try the following:


    1. Create a Running total Column :

     

    running total = CALCULATE(SUM('Table'[M1]),FILTER(ALLEXCEPT('Table','Table'[D1]),[Date]<=EARLIER('Table'[Date])))

     

    2. Then create the count measure: 

     

    Measure = CALCULATE(COUNT('Table'[D4]),FILTER('Table',[running total]>=RELATED('Table (2)'[M2])))

     

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , create a common dimension or create a combined key or both

     

    Key = [D1] & "-" [D2]& "-" [D3]& "-" [D4]

     

    Create a common dimension and display value against it

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak .. I have edited the requirement, please check.I need to implement it in measure since sum(M1) is dynamic depending on selected date.Kindly suggest if there is a way to implement it in measure.