How to pivot rows to columns with dates using the DAX language?

Hi Richtpt,

I hope you are doing well 😀❤️

Ok since you are working with a semantic model and need this for a paginated report...here is my solution:

Using SUMMARIZE and SELECTEDVALUE :

In your paginated report when you set up the dataset that uses the semantic model as Data source use this DAX query:

EVALUATE
SUMMARIZE(
    'TableA',
    'TableA'[WorkID],
    "Requirements",
        CALCULATE(
            SELECTEDVALUE('TableB'[ActivityDate]),
            'TableB'[ActivityName] = "Requirements"
        ),
    "Development",
        CALCULATE(
            SELECTEDVALUE('TableB'[ActivityDate]),
            'TableB'[ActivityName] = "Development"
        ),
    "Testing",
        CALCULATE(
            SELECTEDVALUE('TableB'[ActivityDate]),
            'TableB'[ActivityName] = "Testing"
        ),
    "Production Deployment",
        CALCULATE(
            SELECTEDVALUE('TableB'[ActivityDate]),
            'TableB'[ActivityName] = "Production Deployment"
        )
)
ORDER BY 'TableA'[WorkID]

If you prefer better performance you can try this:

Pivoted Data =
VAR RequirementsTable =
    FILTER(
        'TableB',
        'TableB'[ActivityName] = "Requirements"
    )
VAR DevelopmentTable =
    FILTER(
        'TableB',
        'TableB'[ActivityName] = "Development"
    )
VAR TestingTable =
    FILTER(
        'TableB',
        'TableB'[ActivityName] = "Testing"
    )
VAR ProductionTable =
    FILTER(
        'TableB',
        'TableB'[ActivityName] = "Production Deployment"
    )

RETURN
SUMMARIZE(
    'TableA',
    'TableA'[WorkID],
    "Requirements",
        CALCULATE(
            SELECTEDVALUE('TableB'[ActivityDate]),
            RequirementsTable
        ),
    "Development",
        CALCULATE(
            SELECTEDVALUE('TableB'[ActivityDate]),
            DevelopmentTable
        ),
    "Testing",
        CALCULATE(
            SELECTEDVALUE('TableB'[ActivityDate]),
            TestingTable
        ),
    "Production Deployment",
        CALCULATE(
            SELECTEDVALUE('TableB'[ActivityDate]),
            ProductionTable
        )
)

• Do not forget to change the tables names (based on yours) from your semantic model
• Make sure the activity names exactly match your data
• Use the first query with "EVALUATE" in your dataset connection (For Paginated Reports)

Execute this DAX query in your paginated report dataset (It will return the pivoted data exactly as you wanted)