CODEWITHSUNDEEP
powerpivotdax
Alison Green
Alison Green

Reputation: 19

If Statements For Power Pivot

I'm trying to figure out how to calculate my compliance % measures based on if statements.

If [alias]=company A, then the percentage should equal 100%. If it does not, then it should calculate the total complying spend/total overall spend.

However, when I tried to set up the if statement it gives me an error and says that the single value for "alias" column cannot be determined.

I have tried If(Values) statements, but I need it to return more than one value.

Upvotes: 1

Views: 6513

Answers (1)

Leonard
Leonard

Reputation: 2578

Measures always aggregate. The question is what you want the compliance calculation to be when you're looking at 2 companies? 3 companies? Right now, neither your question nor your formula accounts for this possibility at all, hence the error.

If you're thinking "Compliance % doesn't make sense if you're looking at more than one company", then you can write your formula to show BLANK() if there's more than one company:

IF (
    HASONEVALUE ( 'Waste Hauling Extract'[Alias] ),
    IF (
        VALUES ( 'Waste Hauling Extract'[Alias] ) = "company A",
        [PCT-Compliant],
        [PCT Non-compliant]
    ),
    BLANK ()
)

If you want something else to happen when there's more than one company, then DAX functions like CALCULATE, SUMX or AVERAGEX would allow you to do what you want to do.

The trick with DAX generally is that the formula has to make sense not just on individual rows of a table (where Alias has a unique value), but also on subtotals and grand totals (where Alias does not have a unique value).

Based on your comment that any inclusion of company A results in 100%, you could do something such as:

IF (
    ISBLANK (
        CALCULATE (
            COUNTROWS ( 'Waste Hauling Extract' ),
            FILTER ( 'Waste Hauling Extract', 'Waste Hauling Extract'[Alias] = "company A" )
        )
    ),
    [PCT Non-compliant],
    [PCT-Compliant]
)

The new CALCULATE statement filters the Waste Hauling Extract table to just company A rows, and then counts those rows. If there are no company A rows, then after the filter it will be an empty table and the row count will be blank (rather than 0). I check for this with ISBLANK() and then display either the Non-Compliant or Compliant number accordingly.

Note: the FILTER to just company A only applies to the CALCULATE statement; it doesn't impact the PCT measures at all.

Upvotes: 2

Related Questions