Forum Discussion
Finding repeated values over consecutive dates
jlankford,
I am not very clear about your requirment, do you mean that limit the dates in Matrix to show only 90 days? If so, you can filter dates in visual level filter.
If visual level filter doesn't help, please post complete data of your table and post expected result in screenshot.
Regards,
Lydia
Hi,
Thank you again for your reply, and sorry for the delay in getting back to you.
Below is a screenshot. I used a visual filter to only show the dates 9/28/2017 - 10/6/2017
As you can see, we have ample inventory, and no zeroes. Unfortunately, "ALERT" always reads a number (the reason these numbers are higher than "1" is because I have products belonging to each store that I wish to obscure for now).
In this data source, it contains the most recent 180 days. For some products, we have had good inventory, so I get "0" for an alert, which is what we want. However, other products, we did not begin selling until 90 days ago, meaning that we have 90 days before it where inventory reads "0". Even with the visual filter showing only 90 days, "alert" still reads a "1" for these products.
Thank you again for your help and attention.
Hi jlankford,
Whom are you replying to? If it is to my post, then tell me exactly the scenario where it is not working.
Hi Ashish,
Thanks for your reply. I'm sorry for not specifying, but I was replying to your post.
In your example above in power pivot, that particular measure successfully marks a "1" everytime there is a "0" in the data, but I need it to mark a "1" on any row that has a stretch of five 0's in succession.
For instance:
In the example you provided (see image below), you can see that the row "LMN" has five 0's in succession. I need some indicator to let me know that LMN has a grouping of five 0's.
You can also see in row XYZ there are only two zeros. There is no need to mark these.
I really appreciate everyone's help, and I'm using this as a learning opportunity and not just copy/pasting DAX. Having used standard pivot tables for years, I had no idea there was a whole new world with power pivot and Power BI until just a few months ago.
Thank you.
Maybe you want to learn some Power Query as well? :smileywink:
Actually, Power Query has a function with a special parameter to count consecutive values: Table.Group with 4th parameter GroupKind. If that parameter is set to GroupKind.Local, Table.Group will group consecutive values, so each change in value will become a new group.
So, the challenge is to turn your row values into nested tables and then fire Table.Group on those tables.
This is done with the following query and function GetAlert below. Note that the nested tables are created from all row values except the first 1 (Name). Adjust the Source with your table (I used Power Query in Excel but the rest of the code is fine for Power BI Desktop as well).
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Tables", each Table.FromColumns({List.Skip(Record.FieldValues(_))},{"Values"})), #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "Alert", each GetAlert([Tables])), #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Tables"}) in #"Removed Columns"Function GetAlert:
(MyTable as table) as number => let #"Grouped Rows" = Table.Group(MyTable, {"Values"}, {{"Count", each Table.RowCount(_), type number}}, GroupKind.Local), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Values] = 0 and [Count] >= 5), Alert = if Table.IsEmpty(#"Filtered Rows") then 0 else 1 in Alert
