Is there a Power Bi Dax formula to show a result based on multiple conditions?

Question

I have a Table which contains the following columns:

|       Feature       |       Date       |    Update | hours|
|---------------------|------------------|-----------|------|
|General              |2018-10-02        |V1         |0     |
|General              |2018-10-02        |V1         |0     |
|General              |2018-10-02        |V1         |0     |
|General              |2018-10-02        |V1         |0     |
|General              |2018-10-02        |V1         |0     |
|PvP                  |2018-10-02        |V1         |3     |
|PvP                  |2018-10-02        |V1         |2     |
|PvP                  |2018-10-02        |V1         |1     |
|PvP                  |2018-10-02        |V1         |1     |
|General              |2018-10-02        |V1         |0     |
|General              |2018-10-02        |V1         |7     |
|General              |2018-10-02        |V1         |0     |
|General              |2018-10-02        |V1         |0     |
|Visual               |2018-10-02        |V1         |7     |
|General              |2018-10-15        |V1         |0     |
|General              |2018-10-15        |V1         |0     |
|General              |2018-10-15        |V1         |0     |
|Visual               |2018-10-15        |V1         |7     |
|Visual               |2018-10-15        |V1         |2     |
|General              |2018-10-16        |V1         |1     |

Using this example i want to add a custom column that can count the number of runs executed for each feature based on the following criteria:

Calculate the sum of minutes based on feature for each day and: For example if today's sum is higher than yesterday's, count as 1 run else count as zero.

Below is an example

|       Feature       |       Date       |    Update | hours|No of runs|
|---------------------|------------------|-----------|------|----------|
|General              |2018-10-02        |V1         |0     |1
|General              |2018-10-02        |V1         |0     |1
|General              |2018-10-02        |V1         |0     |1
|General              |2018-10-02        |V1         |0     |1
|General              |2018-10-02        |V1         |0     |1
|PvP                  |2018-10-02        |V1         |3     |1
|PvP                  |2018-10-02        |V1         |2     |1
|PvP                  |2018-10-02        |V1         |1     |1
|PvP                  |2018-10-02        |V1         |1     |1
|General              |2018-10-02        |V1         |0     |1
|General              |2018-10-02        |V1         |7     |1
|General              |2018-10-02        |V1         |0     |1
|General              |2018-10-02        |V1         |0     |1
|Visual               |2018-10-02        |V1         |7     |1
|General              |2018-10-15        |V1         |0     |0
|General              |2018-10-15        |V1         |0     |0
|General              |2018-10-15        |V1         |0     |0
|Visual               |2018-10-15        |V1         |7     |1
|Visual               |2018-10-15        |V1         |2     |1
|General              |2018-10-16        |V1         |1     |1

The run counter should start from 1 for each feature.

So far i have tried to use the following formula but it gives incorrect information:

No_of_runs = CALCULATE(COUNTA('table'[Feature]),Filter('Table','Table'[Feature]=EARLIER('Table'[Feature]) && 'Table'[Date] > EARLIER('Table'[Date] && 'Table'[Time Invested(hours)] > Earlier('Table'[Time Invested(hours)])))

for one thing, you're missing a closing parenthesis after "EARLIER('Table'[Date] " — Ryan B.

Olly Olly · Accepted Answer · 2019-04-09T11:31:41

Not sure I've fully understood the logic, but try this as a calculated column:

No_of_runs = 
VAR DateToday = 
    Table1[Date]
VAR MinutesToday = 
    CALCULATE ( 
        SUM ( Table1[Time Invested (hours)] ),
        FILTER ( 
            ALLEXCEPT ( Table1, Table1[Feature] ),
            Table1[Date] = DateToday
        )
    )
VAR MinutesYesterday = 
    CALCULATE ( 
        SUM ( Table1[Time Invested (hours)] ),
        FILTER ( 
            ALLEXCEPT ( Table1, Table1[Feature] ),
            Table1[Date] = DateToday - 1
        )
    )
RETURN 
    IF ( 
        MinutesToday > MinutesYesterday,
        1,
        0
    )

Edit: here's a worked example PBIX file; https://pwrbi.com/so_55588315/

Is there a Power Bi Dax formula to show a result based on multiple conditions?

1 Answers