Pivot transformation

[Anonymous]

Hello

I have the data:

Field1	Field2	Field3	Field4	Attribute1	Attribute2	Attribute3
1	2	3	4			Yes
2	3	4	4	Yes	Yes
3	4	5	4		Yes

I want to transform the above into:

	Attribute1	Attribute2	Attribute3
	Yes	(blank)	Yes	(blank)	Yes	(blank)
Count of Field1 where Field2 is >2	1	1	2	0	0	0

I would like to do that with Measures or DAX rather than Power Query, any idea?

Thanks!

[Anonymous]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create columns.

blank_1 =
CALCULATE (
    COUNT ( 'Table'[Field1] ),
    'Table'[Field2] > 2
        && 'Table'[Attribute1] = BLANK ()
)

_1yes =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Field1] ),
        FILTER ( 'Table', 'Table'[Field2] > 2 && 'Table'[Attribute1] = "Yes" )
    )
RETURN
    IF ( 'Table'[Attribute1] = "Yes", _count, BLANK () )

blank_2 =
CALCULATE (
    COUNT ( 'Table'[Field1] ),
    FILTER ( 'Table', 'Table'[Field2] > 2 && 'Table'[Attribute2] = BLANK () )
)

_2yes =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Field1] ),
        FILTER ( 'Table', 'Table'[Field2] > 2 && 'Table'[Attribute2] = "Yes" )
    )
RETURN
    IF ( 'Table'[Attribute2] = "Yes", _count, BLANK () )

blank_3 =
VAR count_ =
    CALCULATE (
        COUNT ( 'Table'[Field1] ),
        FILTER ( 'Table', 'Table'[Field2] > 2 && 'Table'[Attribute3] = BLANK () )
    )
RETURN
    IF ( 'Table'[Attribute3] = BLANK (), count_, BLANK () )

_3yes =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Field1] ),
        FILTER ( 'Table', 'Table'[Field2] > 2 && 'Table'[Attribute3] = "Yes" )
    )
RETURN
    IF ( 'Table'[Attribute3] = "Yes", _count, BLANK () )

If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please explain what you mean by "Count of Field1" - maybe COUNTROWS() ?