Forum Discussion
Measure as Column in Matrix
Hi all,
I can see from various other threads that there is no simple way to use a Measure as a column in a matrix. I have looked at the "Disconnected table trick" post but I am struggling to make this work for my particular scenario and I am wondering if this is down to my lack of PowerBI skills or becuase I am working in DirectQuery mode (which of course prohibits the creation of a new column... that would make it too easy of course 🙂 )
So, I have a calculated measure called "Project Name" as below, that runs through all of the contents of "Subscription.Name" and maps to a value that makes more sense to our users, called Project Name
- @Black3y
Is a composite model a possibility for this report? https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models
If so, you could create an allocation table to do this.
Otherwise, you can't measure columns in Power BI. You could put it in Values, but for it to work for the screenshot you've posted, you'd have to create a separate measure for each MoM$ Month (for example, July Mem $, August MoM $, etc.) otherwise you'll end up with the repeated project name for each month.
I guess another option would be to turn off automatic resizing of column widths and make additional project columns result from hidden values as suggested by @Greg_Deckler
Does any of that make sense?
6 Replies
- @Black3y
Is a composite model a possibility for this report? https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models
If so, you could create an allocation table to do this.
Otherwise, you can't measure columns in Power BI. You could put it in Values, but for it to work for the screenshot you've posted, you'd have to create a separate measure for each MoM$ Month (for example, July Mem $, August MoM $, etc.) otherwise you'll end up with the repeated project name for each month.
I guess another option would be to turn off automatic resizing of column widths and make additional project columns result from hidden values as suggested by @Greg_Deckler
Does any of that make sense? Black3y Sorry, having trouble following, can you post sample data as text and expected output?
I can't even tell from your image what is a subscription name or project name because everything is blanked out. Maybe you could include subscription name and project name in the matrix and just shrink the subscription name column/row down to nothing?
Or maybe custom matrix hierarchy? https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.I will try again...
I use the folliing query to create a new meansure called Project Name as "subscription name" means very little to my users.
Project Name = If(CONTAINS('Subscriptions','Subscription'[Subscription Name],"XXXXX"),"New Project Name"
They can then use the report and look at their "Projects".
Desired result below... simply replacing the content of "Subscriptions" field with the contents of "Project Name"
Black3y Yeah, I don't see that happening without it being a composite model per AllisonKennedy
Black3y , Not very clear, but seems like a case of bucketing.
If so you need have measured and then join that measure with and independent table to use that independent tables and you have do it for all measures.
You can refer this example
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/thanks @amitchandak - those links look good but I am not sure I am not sure I know how to use it in this example... I will take a look though.
