Forum Discussion
Only Dimensions Matrix for Row Column and Values
I have a requirement to create a report with only dimension values, there are no numbers and the layout is like what we show in Matrix. Row Column and Values.
Values are also dimensions so I do not want to summarize those. The only option I see is First, Last, Count. But as per requirement need to show multiple text data in values without summarizing. As given in below example - need the cities and the colors in Values whereas the Country and Month/Year in Columns. Further the Cities will have a hyperlink and will need to open a detailed report.
Is there a way to achive this in Power BI Desktop?
Hi Charuta ,
Below is the method I have modeled on your picture.
The original data is like this.
1.Create a Index column in the Power Query.
2.Create a calculated column.
RankColumn = RANKX(FILTER('Table','Table'[Country]=EARLIER('Table'[Country]) && 'Table'[Q-Year]=EARLIER('Table'[Q-Year])),[Index],,ASC,Dense)3.Then get the following view, set the color in Conditional Formatting.
You can check details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Charuta , There is this list disply in this video, see if that can help
https://www.youtube.com/watch?v=WSnB5y2JXv8
Also measure based conditional formatting can help. Create a color measure(example below) and then use that in conditional formatting with field value option
example
Color = if(FIRSTNONBLANK('Table'[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170
,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)steps
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-valuesThanks amitchandak I could get the conditional formatting set up for the colors but I still need the Cities also to be shown in the given format, that doesn't seem to work with Matrix.
Hi Charuta ,
Below is the method I have modeled on your picture.
The original data is like this.
1.Create a Index column in the Power Query.
2.Create a calculated column.
RankColumn = RANKX(FILTER('Table','Table'[Country]=EARLIER('Table'[Country]) && 'Table'[Q-Year]=EARLIER('Table'[Q-Year])),[Index],,ASC,Dense)3.Then get the following view, set the color in Conditional Formatting.
You can check details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a lot Anonymous this worked for me.
