Forum Discussion

Helper V

1 year ago

Creating a custom column to filter data

Hello. I have a very very large table being imported into my dataflow. i have a lot of columns, but for this post, i will shorten it with less columns Title | Category | Date A | E ...

Super User

1 year ago

DAX formula you can use for the new column:

Status = 
IF(
'YourTable'[Category] = "Category B" && 
'YourTable'[Date] < EDATE(TODAY(), -24),
"Exclude",
"Include"
)

You can now apply a visual-level filter or page-level filter to show only rows where Status is "Include".

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

PPStar
Helper V
1 year ago
Hello.
This gives me an error,
Expression.Error: The import IF matches no exports. Did you miss a module reference?
Details
Reason = Expression.Error

also what is the EDATE?
- Angith_Nair
  Continued Contributor
  1 year ago
  PPStar
  Use this DAX formula:
  
  Status = IF( TableName[Category] = "Category B" && TableName[Date] < TODAY() - 730, "Exclude", "Include" )
  
  TODAY() - 730 dynamically calculates a 2-year range by subtracting 730 days from today’s date.
  
  You can then filter your data based on the "Exclude" or "Include" status in the newly created column.
- Anonymous
  Not applicable
  1 year ago
  Hi PPStar ,
  
  Check to make sure you are copying the dax code provided by Kedar_Pande correctly, it should work fine.'' Edate' function works better for your needs, if you simply use 'Today - 720', you may get an error because leap year has 366 days.
  
  Column = IF([_category] = "B" && [Date] < EDATE(TODAY(),-24) ,"exclude","include")
  
  Best Regards,
  Wenbin Zhou

Related content