Measure in Power BI using if statements

2
votes

I want to create a the following measure using if statement:

PRE_MTD_M_visit_cost_v2 =
    if('table_1'[Source_Type]="Online";
        CALCULATE((DIVIDE(21*[MTD_M_visit_cost];7)));
        'table_1'[M_visit_cost])

I want to create a measure with a conditional statement if column Source_Type has “Online” then I want to calculate MTD_M_visit_cost based on the number of passed working days in April (which has 21 working days). If column Source_Type has another text value, I want to return M_visit_cost measure (which I already have created).

PBI returns the following error:

It is impossible to define one value for the "Source_Type" column in the "roistat-analytics-data" table. This can happen if, for a single result, a measure formula refers to a column containing a set of values, without specifying an aggregate, for example MIN, MAX, COUNT, or SUM.

I know that I can create a conditional column, but ideally, it would be great to be able to create a measure. Any help is appreciated.

1
conditionalpowerbimeasure

1 Answers

0
votes

This could be possible, as long as the aggregation of both 'sub-measures' is the same. You can access the values in a row by using the nameX (sumX, avarageX) functions. These functions iterates over a row, so you can make different calculation based on the values in the specific row.

Since you've not provided sample data, I've built a small sample. Hope this is something you can work with. In my example "Sales", I only want to add the VAT to the Amount, if the Sales Type = "Online". For Offline, the VAT will be ignored.

 SalesAmount = 
    SUMX( Sales;
    IF ( Sales[Type] = "Online" ; Sales[Amount] + Sales[VAT] ; Sales[Amount] 
    )
 )

Based on your formula, it will be something like this:

PRE_MTD_M_visit_cost_v2 =
    SUMX (
        table_1;
        IF (
            'table_1'[Source_Type] = "Online";
            DIVIDE ( 21 * [MTD_M_visit_cost]; 7 );
           'table_1'[M_visit_cost]
        )
    )

My sample:

Sample