Run DAX formulas based on values/criteria to produce the required output.

[NickzNickz_BI]

Hi ... 

I have data like below for my dashboard. So far I'm having trouble with the expected results for item no. 4 and no. 5 as table below. 

As we can see, the table above has values that indicate that the KPI indicator is either increasing or decreasing. Achievement column is the activity result for each items.

How can I get the result as in column status?... Can someone help me and advise on this.

Regards.

[v-jialluo-msft]

^{Hi @NickzNickz_BI ,}

^{You can create a measure to calculate quantites:}

Count Not Started = COUNTBLANK('Table'[Achievement])

^{Best Regards,}

^{Gallen Luo}

[NickzNickz_BI]

Hi .. 

Sorry my mistake... that is an example only...

Actually I want to create KPI card as following:

1) Not started = Blank

2) In-Progress = Below Threshold + Threshold + Min

3 Meet Target = Target

I notice that, we cannot create measure over measure ... correct me if wrong ...

Regards,

NickzNickz_BI

[v-jialluo-msft · Accepted solution]

^{Hi @NickzNickz_BI ,}

^{Please follow these steps:}

^{(1) Create a new measure}

Status = 
VAR _THR =
    MAX ( 'Table'[Threshold] )
VAR _MIN =
    MAX ( 'Table'[Min] )
VAR _TAR =
    MAX ( 'Table'[Target] )
VAR _ACH =
    MAX ( 'Table'[Achievement] )
RETURN
    IF (
        ISBLANK ( _ACH ),
        "Not Started",
        IF (
            _TAR > _MIN
                && _MIN > _THR,
            SWITCH (
                TRUE (),
                _ACH >= 0
                    && _ACH < _THR, "Below Threshld",
                _ACH >= _THR
                    && _ACH < _MIN, "Threshld",
                _ACH >= _MIN
                    && _ACH < _TAR, "MIN",
                _ACH >= _TAR, "Meet Target"
            ),
            SWITCH (
                TRUE (),
                _ACH > _THR, "Below Threshld",
                _ACH > _MIN
                    && _ACH <= _THR, "Threshld",
                _ACH > _TAR
                    && _ACH <= _MIN, "MIN",
                _ACH <= _TAR, "Meet Target"
            )
        )
    )

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

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

[NickzNickz_BI]

Hi @v-jialluo-msft ,

Sorry for the late reply due to attending training.

I have run several tests and the results are amazing...

I really appreciate your time and assistance. Thanks a lot...

Btw, can we calculate the result (Status) where equal to Not Started how many (KPI Card) ...

Regards,

NickzNickz_BI

[Greg_Deckler]

@NickzNickz_BI What are the calculation rules?

[NickzNickz_BI]

Hi @Greg_Deckler ,

The formula or calculation method is as below.

When the trend value of threshold, min and target is Increasing:

If Achievement is null, Status is Not Started.

If Achievement is >=0 and < Threshold, Status is Below Threshold.

If Achievement is >= Threshold and < Min, Status is Threshold.

If Achievement is >= Min and < Target, Status is Min.

If Achievement is >= Target, Status is Meet Target

When the trend value of threshold, min and target is Decreasing:

If Achievement is null, Status is Not Started.

If Achievement is > Threshold, Status is Below Threshold.

If Achievement is <= Threshold and > Min, Status is Threshold.

If Achievement is <= Min and > Target, Status is Min.

If Achievement is <= Target, Status is Meet Target.

How can I achieve the output? ... Do I need to add an additional column to indicate whether the column is increasing or decreasing or is DAX able to detect it automatically .... 

Your help and recommendation are highly appreciated.

Regards,

NickNickz_BI