Transform data according to the number of selected values

[Anonymous]

Hi,

I need an idea – maybe someone has one …

The issue:

I have something like a calendar. Any week in the calendar could have a value 0 or 1 (active / inactive week).

The calendar holds several countries, e.g. Spain, GB, Mexico, USA, Canada, Italy …

There is a slicer that allows to select several (!!) countries.

How to visualize this situation in an appealing way?

The first idea is that the X-axis shows the calendar week, #1 to # 53.
The Y-axis indicates if 0 or 1 is valid for each selected country and for each week.
But that means that each country needs a different Y-value if there is a 1 for a specific week.

So let’s say that Spain, USA, and Canada are selected. COUNTROWS() gives me 3 – so Canada’s “1” should be shown as 1, Spain’s “1” should be shown as 2, and USA’s “1” should be shown as 3.

So even in case of a week, where all countries have a 1 there are different lines displayed.

How to realize this using DAX?

Any help is appreciated!

[Anonymous]

Maybe this is something you could do? Because working with '0' values in a chart displays no data, i created a measure where 0 values are displayed as -1. so all the negative bars are inactive weeks for that country, all the positive bars are active weeks.

[Anonymous]

Thank you for the quick answer and the first idea - not sure if the customer accepts this solution.

There is a small drawback: If we have a whole year (43 weeks) and 10 - 15 countries then the plot willbe cluttered.

The customer thinks about sometink like this:

There are at least two problems here:

#1 --> the transformation of the 1 to 1,2,3,... according to the number of selected countries

#2 --> I just want the horizontal lines, not the vertical lines (how to get ride of them?)

... I still collect ideas ... (may there is a better visual - not found until now)

[Anonymous]

Just to reply my own question - part #1

RANKX() does the job!

New Ret Val = RANKX( ALL ( CountryTable) ; CountryTable[Name] ; ; ASC ; Dense)

#2 - how to get rid of the vertical lines is still open.