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 ...
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    7 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.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 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
    7 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