Other methods than pivoting tables

Anonymous — Thu, 15 Jul 2021 09:13:30 GMT

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 🙂

Re: Other methods than pivoting tables

Anonymous — Thu, 15 Jul 2021 09:55:53 GMT

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... ) ) )

topic Other methods than pivoting tables in DAX Commands and Tips

Other methods than pivoting tables

Re: Other methods than pivoting tables