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

[Richtpt]

I'm creating a paginated report that uses a semantic model as the data source.  I'm doing a DAX query to get the data.

I've got data that looks like this:

WorkID	ActivityName	ActivityDate
100	Requirements	2/1/2025
100	Development	2/15/2025
100	Testing	3/1/2025
100	Production Deployment	3/10/2025
105	Requirements	2/3/2025

Each WorkID can have up to 4 rows of data (there are four different ActivityName's).  I want to pivot (unpivot?) the data so each ActivityName is a column heading with the cooresponding date in each column.  Like this:

WorkID	Requirements	Development	Testing	Production Deployement
100	2/1/2025	2/15/2025	3/1/2025	3/10/2025
105	2/3/2025

WorkID is in TableA, the ActivityName and ActivityDate's are in TableB.

I want to do this with the DAX language.  I've found somethings about doing this, but I can't figure out how to do it with dates.  Any suggestions?  Thanks!

[Ahmed-Elfeel · Accepted solution]

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)

WorkID	Requirements	Development	Testing	Production Deployement
100	2/1/2025	2/15/2025	3/1/2025	3/10/2025
105	2/3/2025

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Richtpt]

Thanks!!  I went with a version of the first query and it's working great!

Another follow-up question.  What if WorkID from TableA doesn't have any Activities in TableB?  I still want the WorkID to display.

[Ahmed-Elfeel]

Excellent @Richtpt!

I am glad the first query worked great! 😊

For your follow-up question :

yes the query already handles that perfectly! The way you have it written will still display WorkIDs even if they have no activities in TableB

The SUMMARIZE() function starts with TableA (which contains all WorkIDs) and then for each activity column it uses CALCULATE() with SELECTEDVALUE()

• If no matching activity exists SELECTEDVALUE() returns Blank
• The WorkID still appears in the results from TableA

• The activity columns will just show as blank or null

So the output will be something like this:

WorkID	Requirements	Development	Testing Production	Deployment
100	2/1/2025	2/15/2025	3/1/2025	3/10/2025
105	2/3/2025	null	null	null
110	null	null	null	null

I hope this was helpful for you 😅❤️

Best Regards!

[Richtpt]

Ah, I must have done something wrong.  Thanks again very much, this has helped so much!!