topic Filter a Fact Table to Exclude Item Exceptions By Date Range in DAX Commands and Tips

Filter a Fact Table to Exclude Item Exceptions By Date Range

RGregory — Wed, 15 Mar 2023 16:10:11 GMT

I have a list of transactions by Date and Item (multiple dates and items) and now I need to exclude certain items based on the date ranges in this second table.
(items can appear multiple times - item A is offline between 1/1/2023 and 1/4/2023, but also item A is offline 2/15/2023-2/16/2023)

If the transaction date, for that item appears in one of these entries, it needs to be flagged 1, otherwise 0.

...I did set up an inactive many to many relationship on item ID, so I could call upon item IDs to match them, but filtering between the date fields is tricky.

... also, i do have a date table in this model

Re: Filter a Fact Table to Exclude Item Exceptions By Date Range

Sahir_Maharaj — Wed, 15 Mar 2023 17:06:58 GMT

Hello RGregory,

1. Create a separate table for the exclusion dates with columns for Item, Start Date, and End Date.

2. Create a measure that calculates whether a transaction falls within any of the exclusion date ranges for the item.

Transaction Flag = VAR ItemID = MAX('Transactions'[Item ID]) VAR TransactionDate = MAX('Transactions'[Transaction Date]) VAR Exclusions = FILTER(ExclusionDates, ExclusionDates[Item] = ItemID && TransactionDate >= ExclusionDates[Start Date] && TransactionDate <= ExclusionDates[End Date]) RETURN IF(COUNTROWS(Exclusions) > 0, 1, 0)

3. Add the Transaction Flag measure to your visualizations to see which transactions are flagged based on the exclusion dates.

4. You can also use the measure to filter your data by creating a filter that includes only transactions with a flag of 0 (i.e., transactions that are not excluded).

Let me know if you might require further assistance.

Re: Filter a Fact Table to Exclude Item Exceptions By Date Range

RGregory — Wed, 15 Mar 2023 19:17:24 GMT

Very helpful! Thank you.

I was trying to create this as a calculated column, and doing it the way prescribed above does not work on column.

Instead, I used the steps above to create the Flag as a measure, then used some DAX on column to tie them together.

Thanks!