Showing results for 
Search instead for 
Did you mean: 
New Member

Need help with creating a dynamic column in Power BI

Hello, I'm trying to find a way to turn a measure into a filter (I know I can't add a measure as a filter) and that's why I'm asking for help because I haven't found a way to create a dynamic column that can vary depending on the selected date.


The table is divided into columns of clicks, daily sales, ACOS, and dates, and the mentioned measure has the following formula to know the performance of the search terms of my campaigns.


Performance =

IF (AND ([Clicks] >= 10, [Total Sales per Day] = 0), "Bleeder",

IF (AND ([Clicks] < 10, [Total Sales per Day] = 0), "Untested",

IF ([ACOS] > 0.25, "Unprofitable",

IF (AND ([ACOS] >= 0.15, [ACOS] < 0.25), "Profitable**",

IF ([ACOS] < 0.15, "Profitable", BLANK())))))


The problem is that when I filter a word by a date range, the same word appears in the "Bleeder" and "Profitable" category, for example, because it takes into account the daily result and does not make the total sum...


If anyone can help me, I would really appreciate it.

Thank you!

Memorable Member
Memorable Member

Hi, it is hard to follow what you would like to achieve. Can you post the sample data and the output you want versus what you get? May be field parameters can help.

Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

I have data organized in several columns, such as:

  • Dates
  • Search terms
  • Clicks
  • ACOS
  • Daily sales

When I add a column with a formula, the result is incorrect when I filter by a date range, because the formula performs daily calculations instead of totals for the selected date range and search term in the filter. In the attached image, you can see that when I filter for the search term "plantillas de gel mujer" in a certain date range, the matrix shows correct results for the selected date range as "Profitable**", but in the filter, it shows "Profitable" and "Untested", which is incorrect as it should only display "Profitable**".


Clearly, I have a measure in the matrix and the column with the aforementioned formula in the filters.

Therefore, I am not sure how to get it to display the correct result.


Example measure vs filter.jpg


Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors