Search code examples
filterconditional-statementsdaxpowerquerym

Filter Previous Data DAX on condition

Imagine I have the following dataset.

Product / IsOpen / Date 1 / 0 / 21 / 05 / 2009 1 / 1 / 22 / 05 / 2009

How can I filter/delete data previously to the first IsOpen = 1 ?? By creating a new table or even in Powerquery. I just want data starting from its first IsOpen = 1.

Thanks!

Solution

  • Starting with this table in Power Query:

    enter image description here

    1. Sort by the "Date" column. (Click the down arrow button at the top right of the "Date" column and then select "Sort Ascending".) The table will basically look the same, except the dates will be in order and the down arrow button will change to this:

      enter image description here

      enter image description here

    2. Add an index. (Click the "Add Column" tab and then "Index Column".)

      enter image description here

    3. Group by "IsOpen" and determine the minimum index value for each "IsOpen" value. (Click the "Transform" tab and then "Group By". Then fill in the pop-up window like this:

      enter image description here

      ...and click "OK".) You'll see this:

      enter image description here

    4. Filter out the 0's in "IsOpen". (Click on the down arrow button at the top right of the "IsOpen" column and then deselect "0" in the list.)

      enter image description here

      You'll see this:

      enter image description here

    5. Remove the rows with 0's in "IsOpen" that occur before the first 1. (Here is the "trick" that does what you want. I found it on Ken Puls's blog, "The Ken Puls (Excelguru) Blog":

      Click the "Fx" to the left of the formula bar and overwrite what appears with this:

      = Table.Skip(#"Added Index",#"Filtered Rows"[MinIndex]{0})

      enter image description here

      Then press enter and you'll see this:

      enter image description here

      Notice the 0 "IsOpen" that occurred before the first 1 "IsOpen", which was at "Index" 0, is now gone.)