cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Chris1300
Helper II
Helper II

How to use IF condition in a measure

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 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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.

93.png

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)

vstephenmsft_0-1696401503312.png

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.           

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

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.

93.png

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)

vstephenmsft_0-1696401503312.png

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.           

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
Memorable Member
Memorable Member

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.

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?

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.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors