How to use IF condition in a measure

[Chris1300]

I have a measure that will count Column1 based on filter applied on Columns2 and Columns3.

I want to modify the measure so if the date field is in CURRENT month then also include a 3rd filter criteria in the CALCULATE function as shown. Note, My [Table] does have a calculated column with [Date].

MEASURE = 

CALCULATE(

            COUNT('Table'[Column1])

            ,'Table'[Column2] = 1

            ,'Table'[Column3] = 2

            //,'Table'[Column4] > 20  //I only want to add this filter in the measure if the date field is in CURRENT MONTH

        )

How can I do this?

Thanks,

Chris 

[v-stephen-msft · Accepted solution]

Hi @Chris1300 ,

I created sample data and provided the soltuion.

Sample data is as follows. And the result of counting should be 1 based on your description.

Measure = var _month=MONTH(MAX('Table'[Date]))
var _currentmonth=MONTH(TODAY())
var _count1=CALCULATE(COUNT('Table'[Column1]),FILTER('Table',[Column1]=1&&[Column2]=2))
var _count2=CALCULATE(COUNT('Table'[Column1]),FILTER('Table',[Column1]=1&&[Column2]=2&&[Column3]>20))
return IF(_currentmonth=_month,_count2,_count1)

Best Regards,

Stephen Tao

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

[123abc]

Thank you for providing your solution with sample data. Your approach is correct, and it accomplishes the desired result of counting based on the specified conditions.

Just to summarize, your measure calculates the count of 'Table'[Column1] with the following logic:

1. If the month of the date in the row matches the current month, it applies the additional filter condition `[Column3] > 20`.
2. If the month of the date in the row doesn't match the current month, it counts without the additional filter condition.

Your solution effectively handles the scenario, and it will count as 1 based on the provided data and conditions. Thank you for sharing it....

Now i will working on you pbix file and will reply you with hand on solution.

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

[123abc]

In Power BI, you can use the IF function to conditionally apply filters to your measure based on whether the date field is in the current month. To achieve this, you can use the following approach:

MEASURE =
VAR CurrentMonthFilter =
IF (
YEAR ( TODAY () ) = YEAR ( MAX ( 'Table'[Date] ) ) &&
MONTH ( TODAY () ) = MONTH ( MAX ( 'Table'[Date] ) ),
// Add the filter for the current month
FILTER ( 'Table', 'Table'[Column4] > 20 ),
// If not in the current month, use an ALL filter for Column4
ALL ( 'Table'[Column4] )
)
RETURN
CALCULATE (
COUNT ( 'Table'[Column1] ),
'Table'[Column2] = 1,
'Table'[Column3] = 2,
CurrentMonthFilter
)

In this measure:

1. We use the VAR keyword to define a variable called CurrentMonthFilter.

2. Within CurrentMonthFilter, we check if the date field in your table is in the current month. We use TODAY() to get the current date and compare it with the date from your table.

3. If the date is in the current month, we apply the filter condition 'Table'[Column4] > 20) to CurrentMonthFilter.

4. If the date is not in the current month, we apply an ALL filter to 'Table'[Column4] to remove any filtering effect.

5. Finally, in the CALCULATE function, we use the CurrentMonthFilter variable as one of the filter conditions along with your existing filter conditions on Column2 and Column3.

This way, the Column4 filter is applied conditionally based on whether the date is in the current month or not, as specified in your question.

[Chris1300]

I tried this but getting "The expression refers to multiple columns. Multiple columns cannot be converted to a scaler value".

Any idea why this is happening?

[123abc]

The error message "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value" typically occurs when you're trying to use a table or a column reference where a single value (scalar) is expected in a DAX expression. In the modified measure I provided, the error might be related to how the FILTER function is used. To resolve this issue, you can wrap the FILTER function inside a SUMX or MAXX function to aggregate the result into a single value. Here's the updated measure:

MEASURE =
VAR CurrentMonthFilter = IF(
YEAR(MAX('Table'[Date])) = YEAR(TODAY()) && MONTH(MAX('Table'[Date])) = MONTH(TODAY()),
20, // Apply the filter when the date is in the current month
BLANK() // Don't apply the filter otherwise
)
RETURN
CALCULATE(
COUNT('Table'[Column1]),
'Table'[Column2] = 1,
'Table'[Column3] = 2,
FILTER('Table', 'Table'[Column4] > CurrentMonthFilter),
'Table'[Column4] // This is required for the aggregation function (SUMX or MAXX) to work correctly
)

In this updated measure, I've included 'Table'[Column4] inside the CALCULATE function along with the FILTER function. This is necessary to provide an additional column to the aggregation function (SUMX or MAXX). Depending on your specific needs, you can replace 'Table'[Column4] with any column that makes sense in your context for aggregation. This change should resolve the error you were encountering.

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