Only Dimensions Matrix for Row Column and Values

[Charuta]

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?

[Anonymous · Accepted solution]

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.

[Charuta]

Thanks a lot @Anonymous this worked for me. 

[amitchandak]

@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-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Charuta]

Thanks @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.