Count of changes from Pass to Fail and vice versa

[AnoopNair]

Hi All,
I am very new to Power BI and hoping to get a response from the excellent community from which I am learning a great deal.

Below is my sample data and structure: In realtime I have a SQL DB and using a direct query to fetch the data to report. There are other columns in this table but omitting them as I believe they dont add value to this question. 

TestTitle	BuildDate	Outcome
Test1	01/01/2021	Pass
Test2	01/01/2021	Pass
Test1	01/02/2021	Pass
Test2	01/02/2021	Fail
Test1	01/03/2021	Fail
Test2	01/03/2021	Fail
Test1	01/04/2021	Pass
Test2	01/04/2021	Fail

I need to calculate a factor called AF for each test which is the count of the changes from pass to fail or vice versa divided by the total number of occurrences.
i.e.
AF for Test1 is 2/4 = 0.5  (since Test1 changed from Pass to Fail once and Fail to Pass once, hence 1+1 =2. Divided by 4 occurences of Test1)
AF for Test2 is 1/4 = 0.25

I need these values to be shown in a table visual which has unique entries for each test as a representation as below:

TestTitle	AF	Pass%
Test1	0.5	75%
Test2	0.25	25%

There will be other columns in this visual as well. A slicer will be used filter the tests to be shown in the table visual. 

I tried many options from other posts similar to this where we store the previous row and compare that with that with current one. But unfortunately not able to pin point the fix.

Thanks in advance,

Regards,
Anoop Nair

[Anonymous]

This should work as all the functions used are supported for DQ. Not sure about the performance, though... You'd need to check it and report. I'll try to look for a better formulation of the measure as well.

[# Changes] =
sumx(
	distinct( T[TestID] ) , -- must be a unique identifier of tests
	// this calculate makes sure that all the
	// calculations below are done for the
	// test that is being iterated
	calculate(
		var RankedByBuildDate =
			// we're taking all the rows visible
			// for the test currently being iterated
			// and adding to each row an integer that
			// establishes the temporal sequence by
			// the build date; note that the assumption
			// here is that each test can have at most
			// one entry in the T table for any single
			// day; this is a condition that your table
			// shown implies
			addcolumns(
				T,
				"@SeqNo", -- sequence number
					var CurrentBuildDate = T[BuildDate]
					return
					rankx(
						T,
						T[BuildDate],
						CurrentBuildDate,
						ASC,
						DENSE
					)
			)
		var PairedOutcomes =
			selectcolumns(
				RankedByBuildDate,
				"@Outcome", T[Outcome],
				"@NextOutcome",
					var CurrentSeqNo = [@SeqNo]
					return
					maxx(
						// can use maxx here since
						// the filter will return
						// at most one row
						filter(
							RankedByBuildDate,
							[@SeqNo] = CurrentSeqNo + 1
						),
						T[Outcome]
					)
			)
		var DifferentPairs =
			filter(
				PairedOutcomes,
				// if the outcome is different from
				// the next outcome...
				[@Outcome] <> [@NextOutcome]
				&&
				// and the next outcome is not blank
				// (which is true for the last row)
				// then we've got a change
				not isblank( [@NextOutcome] )
			)
		var Output = countrows( DifferentPairs )
		return
			Output		
	)
)

and a slightly different formulation:

# Changes2 = 
SUMX(
    DISTINCT( T[TestID] ),
    CALCULATE(
        var SequencedRows =
            ADDCOLUMNS(
                T,
                "@SeqNo",
                    var CurrentBuildDate = T[BuildDate]
                    return
                        RANKX(
                            T,
                            T[BuildDate],
                            CurrentBuildDate,
                            ASC, Dense
                        )
            )
        var Changes =
            FILTER(
                SequencedRows,
                var CurrentOutcome = T[Outcome]
                var CurrentSeqNo = [@SeqNo]
                return
                    NOT ISEMPTY(
                        FILTER(
                            SequencedRows,
                            [@SeqNo] = CurrentSeqNo + 1
                            &&
                            T[Outcome] <> CurrentOutcome
                        )
                    )
            )
        return
            COUNTROWS( Changes )
    )
)

[AnoopNair]

Thanks a lot @Anonymous . This is a life saver.

This works perfectly given the criteria that a test occurs once a day. 

But unfortunately there is a slight twist of fate. When I went through the bulk of records I saw that there are 2 more additional columns that could affect the uniqueness along with the build date called Release and Environment that can vary.

i.e. a test can repeat on the same build date for a different release and enviornment combination.

TestTitle	BuildDate	Release	Environment	Outcome
Test1	01/01/2021	Rel1	Ev1	Pass
Test2	01/01/2021	Rel 3	Ev4	Pass
Test1	01/02/2021	Rel1	Ev2	Pass
Test1	01/02/2021	Rel1	Ev2	Fail
Test2	01/02/2021	Rel3	Ev4	Fail
Test1	01/03/2021	Rel2	Ev3	Fail
Test2	01/03/2021	Rel3	Ev4	Fail
Test1	01/04/2021	Rel2	Ev3	Pass
Test2	01/04/2021	Rel3	Ev4	Fail

So we'll need the factor for the test with the Release and Env combination.

I am currently trying now to tweak this measure to accomodate that. But if you have any quick ideas please do let me know.

Very sorry for the hindsight. There were a few records that exhibited this variation that didnt show up in my list.

Please let me know if you have further questions.

Thanks and Regards,

Anoop

[Anonymous]

@AnoopNair 

2 things here. First, you know now that when you post data it has to be REPRESENTATIVE. Second, you have not given "the rules of the game" when your data looks like the last version. Therefore I can't write any code. If there are multiple versions of a test on the same day, you have to formulate the algorithm for precisely determining what "a change" means and when it appears.

[AnoopNair]

Thank you for your response. Sorry @Anonymous if I have offended you. That was not the intention.

If you agree I shall mark your previous answer as the accepted solution as that was right based on my initial query.

Thanks again.

Regards,

Anoop

[Anonymous]

@AnoopNair 

Don't be sorry 'cause you don't have to 🙂 Cheer up! All you have to do now is to state the exact rules by which I'd be able to write code for you. Is that OK with you?

[AnoopNair]

Thanks @Anonymous .

I shall try to explain better.

Apologies for not showing the actual data and representation because of policies.

So below is a representation of my table (updated with the Release and Environment columns) for 3 days of run.

Please note the following:

1) Environment is a child of Release.

2) A Release can have multiple children (Environments).

3) Each test can run for multiple Releases and thereby will be part of at least one Release + Environment combo.

4) A test + Rel + Env combination will be unique for the Build Date.

The goal is to get a table visual as below. I have mentioned the description of the AF column in the last column.

The page would have slicer controls to filter based on Releases and Environments.

If you have specific questions, please let me know.

Thank you for your time.

Regards,

Anoop

[Anonymous · Accepted solution]

The change to the code is rather simple:

[# Changes] =
SUMX(
    SUMMARIZE( 
        T,
        T[TestID],
        T[Release],
        T[Environment]
    ),
    ... // this code as before
)

[AnoopNair]

Thaaaaaanks a lot @Anonymous. You are a life saver... I'll reserve a beer in your name.. 

I just need to divide this change count with the number of tests. I got that done.

Regards,

Anoop

[amitchandak]

@AnoopNair , Create a new column and you can use sum of that

New column =
var _max = maxx(filter(table,[TestTitle] = earlier([TestTitle]) && [BuildDate] <earlier([BuildDate])),[BuildDate])
var _outcome = maxx(filter(table,[TestTitle] = earlier([TestTitle]) && [BuildDate] = _max ),[outcome])
return
if(_outcome =[outcome] , blank(), 1)

[AnoopNair]

Thanks Amit,

  But I get an error saying

"Function 'MAXX' is not allowed as part of a calculated column DAX expressions on DirectQuery models.

Regards,

Anoop Nair

[AnoopNair]

Hi @amitchandak . It was mentioned in the original query that it was a Direct Query model from SQL DB. Sorry if it was not highlighted, 

Regards,

Anoop Nair

[amitchandak]

@AnoopNair , Sorry forget to notice that

Try as measure

New measure =
var _max = maxx(filter(allselected(table),[TestTitle] = max([TestTitle]) && [BuildDate] <max([BuildDate])),[BuildDate])
var _outcome = maxx(filter(allselected(table),[TestTitle] = max([TestTitle]) && [BuildDate] = _max ),[outcome])
return

sumx(summarize(Test, Test[BuildDate], Test[TestTitle], "_1", if(_outcome =[outcome] , blank(), 1)),[_1])

/////

or replace return with

sumx(addcolumns(summarize(Test, Test[BuildDate], Test[TestTitle]), "_1", if(_outcome =[outcome] , blank(), 1)),[_1])

[AnoopNair]

Thanks @amitchandak.

Unfortunately I am getting the below error with the measure: ( I changed the column and table name as per the original table query)

"A single value for column 'outcome' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

My outcome column is not summarized.

Regards,

Anoop Nair

[AnoopNair]

Hi @amitchandak ,

   Would be greatful if you could have a look at the error please?

Thanks in advance.

Regards,

Anoop

[AnoopNair]

Kindly have a look please.. I am royally stuck without a solution.

 @Mohammad_Refaei , @Anonymous , @Greg_Deckler , @wdx223_Daniel , @Fowmy , @Jos_Woolley , @Jihwan_Kim 

Thanks and Regards,

Anoop Nair

[Mohammad_Refaei]

I would create a calculated column to visualize the change

Last Outcome = 
VAR PreviousDate =
    MAXX (
        FILTER (
            Data,
            [TestTitle] = EARLIER ( [TestTitle] )
                && [BuildDate] < EARLIER ( [BuildDate] )
        ),
        [BuildDate]
    )
RETURN
    COALESCE (
        CALCULATE (
            TOPN ( 1, VALUES ( Data[Outcome] ) ),
            FILTER ( ALLEXCEPT ( Data, Data[TestTitle] ), Data[BuildDate] = PreviousDate )
        ),
        Data[Outcome]
    )

Then use a measure like this:

AF =
DIVIDE (
    CALCULATE (
        COUNTROWS ( Data ),
        FILTER ( Data, Data[Outcome] <> Data[Last Outcome] )
    ),
    CALCULATE ( COUNTROWS ( Data ), ALLEXCEPT ( Data, Data[TestTitle] ) )
)

And this would be the outcome

I hope this solves your problem.

Please check this sample file.

[Anonymous]

@Mohammad_Refaei 

I don't think you can create a calc column in a DQ model, can you? Well, have just checked it: No, you can't.

[Mohammad_Refaei]

@Anonymous , you are right it won't work. I missed this fact as I don't rely on it that much.

The suggested measure could be adjusted to include the same logic in the calculated column.

I am out and won't be available to fix it before 3 hours. Would be great if you can adjust it, seems that @AnoopNair needs it urgently.

[Anonymous]

Wish I could! I have a meeting with my boss in about 8 mins... Sorry!

[AnoopNair]

Highly appreciate the response. @Mohammad_Refaei and @Anonymous .

  I had tried a similar option of the calculated column. But as @Anonymous mentioned Direct Query plays a spoil sport.