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 

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
    7 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.