Other methods than pivoting tables

[Anonymous]

Hi,

In order to count each variable based on the value I have pivoted the table below:

Into:

This is just parts of all the data. I tried to make the pivot on all of the dataset but I stopped the process when it reached 2,5 millions rows.

Is there any ways to do this without pivoting the table?

Thanks in advance 🙂

[Anonymous · Accepted solution]

Of course, there is. There always is. Create a cross-join table with this makeup:

Value|VariableName

----------------------

failed|Var1

not_tested|Var1

passed|Var1

failed|Var2

... and so on

This is a disconnected table.

then create a measure:

[Status Count] =
CALCULATE(
	SUMX(
		SUMMARIZE(
			T,
			T[Value],
			T[VariableName]
		),
		var Status_ = T[Value]
		var VarName = T[VariableName]
		return
		switch( VarName,
			"Var1",
				CALCULATE(
					COUNTROWS( YourTable ),
					KEEPFILTERS(
						YourTable[Var1] = Status_
					)
				),
			"Var2",
				CALCULATE(
					COUNTROWS( YourTable ),
					KEEPFILTERS(
						YourTable[Var2] = Status_
					)
				),
			// ... and so on in the same fashion...
		)
	)
)