Creating a table with SUMMARIZE() to get a correct count of cycle times

[powerquest1234]

I have a table that shows information on Zoo ID #s, Specimen ID #s, and the current "stage" the specimen is in (ie for a frog, this would be egg, tadpole, or frog). 

My client wants the number of days between the beginning of the Tadpole stage and the end of the Frog stage.
- The data table has a row for each specimen's stage, showing it's start date and end date (ie there is a separate row for the Frog Egg stage, the Tadpole Stage, the Frog stage, but these are all the same animal--specimen ID #95).

- Each zoo has one specimen per species ever (so none of the zoos have a tadpole and a frog at the same time, or multiple frogs, or multiple tadpoles--it's always just one specimen per species. Once the frog dies or is given away, the zoo will never get another tadpole/frog/etc).
- You'll notice the Tadpole Start Date, Tadpole End Date, Frog Start Date, Frog End Date columns, Tadpole -> Frog (number of days) populate for every row in the Zoo ID, even on animals that don't have these stages. 
- The Tadpole -> Frog (number of days) is the number of days between the Tadpole Start Date and the Frog End Date. This is blank if there is NOT a Frog End Date.
- The rows will not always be in this same order

Zoo ID	Specimen ID	State	Specimen Stage	Stage Start Date	Stage End Date	Tadpole Start Date	Tadpole End Date	Frog Start Date	Frog End Date	Tadpole Start Date to Frog End Date Cycle Time (number of days)
2	3	Wyoming	Frog egg	3/18/2017	4/1/2017	4/1/2017	5/1/2017	5/1/2017	10/21/2017	203.00
2	3	Wyoming	Tadpole	4/1/2017	5/1/2017	4/1/2017	5/1/2017	5/1/2017	10/21/2017	203.00
2	3	Wyoming	Frog	5/1/2017	10/21/2017	4/1/2017	5/1/2017	5/1/2017	10/21/2017	203.00
1	95	Texas	Frog egg	1/10/2018	1/21/2018	1/21/2018	3/4/2018	3/4/2018	11/1/2018	284.00
1	95	Texas	Tadpole	1/21/2018	3/4/2018	1/21/2018	3/4/2018	3/4/2018	11/1/2018	284.00
1	95	Texas	Frog	3/4/2018	11/1/2018	1/21/2018	3/4/2018	3/4/2018	11/1/2018	284.00
1	2	Texas	Butterfly egg	3/1/2019	3/15/2019	1/21/2018	3/4/2018	3/4/2018	11/1/2018	284.00
1	2	Texas	Caterpillar	3/15/2019	3/19/2019	1/21/2018	3/4/2018	3/4/2018	11/1/2018	284.00
1	2	Texas	Cocoon	3/19/2019	4/1/2019	1/21/2018	3/4/2018	3/4/2018	11/1/2018	284.00
1	2	Texas	Butterfly	4/1/2019	4/15/2019	1/21/2018	3/4/2018	3/4/2018	11/1/2018	284.00
4	6	Texas	Tadpole	10/5/2020	2/25/2020	10/5/2020	2/25/2020	2/25/2020	1/28/2021	115.00
5	82	Idaho	Frog egg	3/1/2020	4/5/2020	4/5/2020	8/9/2020	8/9/2020
4	6	Texas	Snake egg	4/29/2020	5/16/2020	10/5/2020	2/25/2020	2/25/2020	1/28/2021	115.00
5	82	Idaho	Tadpole	4/5/2020	8/9/2020	4/5/2020	8/9/2020	8/9/2020
4	6	Texas	Frog egg	10/1/2020	10/5/2020	10/5/2020	2/25/2020	2/25/2020	1/28/2021	115.00
3	5	Wyoming	Frog egg	10/5/2020	10/19/2020	10/19/2020
4	6	Texas	Frog	2/25/2020	1/28/2021	10/5/2020	2/25/2020	2/25/2020	1/28/2021	115.00
1	49	Texas	Parrot egg	7/1/2021	8/1/2021	1/21/2018	3/4/2018	3/4/2018	11/1/2018	284.00
5	10	Idaho	Parrot egg	4/21/2022	5/1/2022
2	4	Wyoming	Monkey	7/1/2016		4/1/2017	5/1/2017	5/1/2017	10/21/2017	203.00
3	5	Wyoming	Tadpole	10/19/2020		10/19/2020
4	6	Texas	Snake	5/16/2020		10/5/2020	2/25/2020	2/25/2020	1/28/2021	115.00
4	6	Texas	Coyote			10/5/2020	2/25/2020	2/25/2020	1/28/2021	115.00
5	10	Idaho	Parrot	5/1/2022
5	36	Idaho	Goat			4/5/2020	8/9/2020	8/9/2020
1	49	Texas	Parrot	8/1/2021		1/21/2018	3/4/2018	3/4/2018	11/1/2018	284.00
5	54	Idaho	Seal			4/5/2020	8/9/2020	8/9/2020
5	82	Idaho	Frog	8/9/2020		4/5/2020	8/9/2020	8/9/2020

The issue I'm running into is that when I pull this dataset into Power BI and create a "Table" visual, I can't seem to get an accurate count of cycle times. For example, Zoo ID #1 only has one frog specimen and one cycle time. However, because the Tadpole Start Date, Tadpole End Date, Frog Start Date, Frog End Date and Tadpole to Frog Cycle Time columns populates on all nine rows for that zoo (even on the non-frog animals), this returns a count of 9 cycle times when it should really just count one cycle time for the only frog specimen. This is because every zoo in my hypothetical scenario will ever only have one frog.

I am thinking my next option is to use power query to create a cycle time column that only populates under particular conditions and pivot if necessary (so there's only one Tadpole to Frog cycle time per zoo). However, I find that I am over-relying on Power Query lately so I am curious if there is a way to get to an accurate cycle time count (one cycle time per zoo) with DAX. 

I tried adapting this code to my situation ( https://www.sqlbi.com/articles/understanding-data-lineage-in-dax/ ) but I'm not sure how I would filter out rows that are missing Frog End Dates (and thus no cycle time can be calculated) or filter only for zoos within a particular state. Is there a tutorial that someone would recommend?

[v-yiruan-msft · Accepted solution]

Hi @powerquest1234 ,

You can create a measure as below to get the count of cycle times which include Tadpole to Frog Cycle Time, please find the details in the attachment.

Count of cycle times = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Specimen ID] ),
    FILTER (
        'Table',
        'Table'[Zoo ID] = SELECTEDVALUE ( 'Table'[Zoo ID] )
            && 'Table'[Specimen Stage] IN { "Tadpole", "Frog" }
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards