Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Coriel-11
Resolver I
Resolver I

Create a Matrix that Gathers Dates for each category, not vice versa (like in Excel)

Hi everyone,
I have data in PowerBI that looks a bit like this:

Coriel11_0-1649865219295.png

 

and I want to display it in a matrix like you can in a pivot table like this:

 

Coriel11_1-1649865285004.png

Basically, I want it to gather dates under each category, (rather than what Power BI does which is gather the categories under each date/month like this:

Coriel11_2-1649865408766.png

Is there any way around this? It seems kinda basic, but also not really possible.

[I did wonder if a measure could be created which would switch depending on the column, but lack the DAX skills + I'm not sure it gets me any further forward]

 

Any ideas?

Thank you,

Matt

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Here is a way. I'm assuming the periods are actual dates, for which I've created a Calendar Table and a Period Table (the latter needed to link to a new header table). If the periods are not dates, all you need in the model is the Period Table.

dates tables.jpg

 Create a new table using the "New Table" option under Modeling in the ribbon and the following code:

 

Header Table = 
VAR Metric = {("Sum Clicks", 1), ("Sum Page Views", 2)}
VAR Period = VALUES('Calendar Table'[Month-Year])
RETURN
CROSSJOIN(Metric, Period)

 

header table.jpg

 

Create a relationship between the period table and the corresponding field in the Header Table. The model is as follows:

model.jpg

 With simple SUM measures for Clicks and Page Views, create the following measure to use in the matrix visual:

 

Matrix values =
SWITCH (
    SELECTEDVALUE ( 'Header Table'[Order] ),
    1, [Sum CLicks],
    2, [Sum Page Views]
)

 

Now create the matrix visual using the web page field, the Metric field from the header table, the Period field from the Period table and the [Matrix Values] measure to get:

layout.jpgI've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

Here is a way. I'm assuming the periods are actual dates, for which I've created a Calendar Table and a Period Table (the latter needed to link to a new header table). If the periods are not dates, all you need in the model is the Period Table.

dates tables.jpg

 Create a new table using the "New Table" option under Modeling in the ribbon and the following code:

 

Header Table = 
VAR Metric = {("Sum Clicks", 1), ("Sum Page Views", 2)}
VAR Period = VALUES('Calendar Table'[Month-Year])
RETURN
CROSSJOIN(Metric, Period)

 

header table.jpg

 

Create a relationship between the period table and the corresponding field in the Header Table. The model is as follows:

model.jpg

 With simple SUM measures for Clicks and Page Views, create the following measure to use in the matrix visual:

 

Matrix values =
SWITCH (
    SELECTEDVALUE ( 'Header Table'[Order] ),
    1, [Sum CLicks],
    2, [Sum Page Views]
)

 

Now create the matrix visual using the web page field, the Metric field from the header table, the Period field from the Period table and the [Matrix Values] measure to get:

layout.jpgI've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @PaulDBrown,

Thank you so much for all the effort you put into explaining that and making the sample pbix as well. It works!! I even managed to tweak it to include info from two different query tables. I will be bookmarking this one as I know I will use it again.


Just off now to try and find out about CROSSJOIN as I haven't come across that before.

 

With many thanks and much appreciation,

Matt

PC2790
Community Champion
Community Champion

See if the similar post helps.

Thank you. I did see that one (as was going to use it as a fall-back option). I just already have a lot going on in Power Query (15 min refresh time) so I was hoping for something more DAX based. It just seems like it should be more straightforward to do this so I was hoping someone might have a different approach.

Matt

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.