Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX calculation help

Long post alert! I am sorry, tried my best in explaining what I need..

I have a table that looks as follows:

 

 

 

I would like to calculate 2 measures from this:

 

1. Other Phase = Count of ID where there is a from phase and to phase is everything except the from phase itself and the next phase itself.

For Ex:  Those in red font are examples. If we take From Phase = 1, then To Phase = 3,4,5,6 (We should not consider the From phase itself, which is 1 and the Next phase which is 2)

Similarly, if from phase = 2, to phase = 1,4,5,6 (No 2 and 3)

THE PHASE 6 IS THE LAST PHASE. Hence, if from phase is 6 it should always give me 0

 

2. Outside Role = Count of ID where there is a from phase and to phase is not equal to any phase.

For Ex: Those in blue font are examples. If From Phase = 1 then Tp Phase <>1,2,3,4,5,6 (All phases included) 

Similarly, if From Phase = 2, To phase<1,2,3,4,5,6>. In this case a person maybe have from phase = 6 but to phase <>1,2,3,4,5,6 So phase 6 will also have a number. 

 

Ideally, my output matrix visual should look like this:

 

I tried the following DAX code, but the output numbers were not right. So I was not sure where i was going wrong:

 

Other Phase = CALCULATE(DISTINCTCOUNT(ACTIVITY[ID]) ,FILTER(ACTIVITY, ACTIVITY[From Phase]=ACTIVITY[From Phase] && ACTIVITY[To Phase] <> ACTIVITY[From Phase] && ACTIVITY[To Phase] <> ACTIVITY[From Phase]+1))+0
(Did not also know how to add if phase = 6, then 0)
 
Outside Phase = CALCULATE(DISTINCTCOUNT(ACTIVITY[ID]), FILTER(ACTIVITY, ACTIVITY[From Phase] = ACTIVITY[From Phase] && ACTIVITY[To Phase] <> 1 || ACTIVITY[To Phase] <> 2 || ACTIVITY[To Phase] <> 3 || ACTIVITY[To Phase] <> 4 || ACTIVITY[To Phase] <> 5 || ACTIVITY[To Phase] <> 6))+0
 
Appreciate your time and help! Thank you!
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    When modifing with my formula, it seems to work

    Create a column

    outside column 2 =
VAR t1 =
    FILTER ( VALUES ( Sheet1[From PHA_GATE_TXT] ), [From PHA_GATE_TXT] <> BLANK () )
VAR t2 =
    VALUES ( Sheet1[To PHA_GATE_TXT] )
RETURN
    CALCULATE ( COUNT ( Sheet1[ID2] ), EXCEPT ( t2, t1 ) )

    Create two measures

    outside 2 =
CALCULATE (
    COUNT ( Sheet1[ID2] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[From PHA_GATE_TXT] ),
        [outside column 2] <> BLANK ()
    )
)

other 2 =
CALCULATE (
    COUNT ( Sheet1[ID2] ),
    FILTER (
        ALLSELECTED ( Sheet1 ),
        Sheet1[From PHA_GATE_TXT] = MAX ( Sheet1[From PHA_GATE_TXT] )
            && ( Sheet1[To PHA_GATE_TXT] <> MAX ( Sheet1[From PHA_GATE_TXT] ) )
            && (
                Sheet1[To PHA_GATE_TXT]
                    <> MAX ( Sheet1[From PHA_GATE_TXT] ) + 1
            )
            && [From PHA_GATE_TXT] <> BLANK ()
            && [To PHA_GATE_TXT] <> BLANK ()
    )
)

    Best Regards
    Maggie

     

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

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    When modifing with my formula, it seems to work

    Create a column

    outside column 2 =
VAR t1 =
    FILTER ( VALUES ( Sheet1[From PHA_GATE_TXT] ), [From PHA_GATE_TXT] <> BLANK () )
VAR t2 =
    VALUES ( Sheet1[To PHA_GATE_TXT] )
RETURN
    CALCULATE ( COUNT ( Sheet1[ID2] ), EXCEPT ( t2, t1 ) )

    Create two measures

    outside 2 =
CALCULATE (
    COUNT ( Sheet1[ID2] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[From PHA_GATE_TXT] ),
        [outside column 2] <> BLANK ()
    )
)

other 2 =
CALCULATE (
    COUNT ( Sheet1[ID2] ),
    FILTER (
        ALLSELECTED ( Sheet1 ),
        Sheet1[From PHA_GATE_TXT] = MAX ( Sheet1[From PHA_GATE_TXT] )
            && ( Sheet1[To PHA_GATE_TXT] <> MAX ( Sheet1[From PHA_GATE_TXT] ) )
            && (
                Sheet1[To PHA_GATE_TXT]
                    <> MAX ( Sheet1[From PHA_GATE_TXT] ) + 1
            )
            && [From PHA_GATE_TXT] <> BLANK ()
            && [To PHA_GATE_TXT] <> BLANK ()
    )
)

    Best Regards
    Maggie

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-juanli-msft Thank you! Just a quick question, how do we match the total column for outside phase to be equal to the sum of the values. for now 13445 total is wrong. It should be 13666 if we calculate the sum. 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Create a column

    outside column =
VAR t1 =
    VALUES ( Sheet2[from phase] )
VAR t2 =
    VALUES ( Sheet2[to phase] )
RETURN
    CALCULATE ( COUNT ( Sheet2[id] ), EXCEPT ( t2, t1 ) )

    Create two measures

    other =
CALCULATE (
    COUNT ( Sheet2[id] ) + 0,
    FILTER (
        ALLEXCEPT ( Sheet2, Sheet2[from phase] ),
        ( Sheet2[to phase] <> MAX ( Sheet2[from phase] ) )
            && (
                Sheet2[to phase]
                    <> MAX ( Sheet2[from phase] ) + 1
            )
            && [to phase] <> BLANK ()
    )
)


outside = CALCULATE(COUNT(Sheet2[id]),FILTER(ALLEXCEPT(Sheet2,Sheet2[from phase]),[outside column]<>BLANK()))

    Best Regards
    Maggie

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-juanli-msft  Hey, thanks for your time! It works in the dummy dataset I have given, however when i repace the formula for my actual data set:

       

      1. For outside, it shows only blank

       

      2. For others measure, I am getting a wrong output. Not sure why. But, in the real data i have records that look like this:

       

      ID         From        To

      123                         1

      123            1           1

      345                         2

      345            2           2

       

      and many more. Could this be a possible reason? But I have done a distinct count in my formula.           I have given another version of data that replicates my actual data set for your reference!

      (Excel)

       

        https://drive.google.com/open?id=1fuHQvDWwaFdP_JwFVDfcwl9hisSnZAmq