Different Value in X Axis displayed than data being shown?

[dutch070]

Hello, I'm creating a leads competition report where on one page an Admin will set a competition Start Date and End date (this is a slicer that filters a calculated calendar table I have) and a parameter which sets total target leads they should be targeting. My trouble is that my customer wants to show a stacked column chart to show how many they have for the weeks (WeekNumber) that was selected in the previously mentioned StartDate and EndDate but the customer doesn't want to show the actual WeekNumbers of the competition, they want to see just Week 1,2,3,4,5,etc of the actual competition week. I cant create a measure because you can't use measures for X-axis in stacked column charts so im trying to create a calculated column inside my calendar table with a Rankx or index but that wont update based on the slicer. I NEED HELP PLEASE! Can this be done?

I have this currently (updated based on feedback given below)

I want it to LOOK like this but it has to still pull the data from my data model related to the weeks of the leads. SO it cant actually be Weeknumber 1 because Im needing to show the data from the competition during time period. Hope that makes sense

HELP PLease!

[parry2k]

@dutch070 as reading more on your question, check out these 3 videos on my channel which talks about similar stuff. HTH

Provide insight into the data by using cohort analysis to understand what products are performing (y...

Learn how a small model change and DAX helped to develop dynamic and scalable cohort analysis (youtu...

In this video on cohort analysis learn how to do analysis on any dimension-A true scalable solution ...

[dutch070]

Thank you for your response. I watched your cohort analysis video and that’s very helpful however, can I create a dynamic calculated column based on a slicer that can change that data? If my customer changes the slicer for the leads competition to start on 6/3/24, will my calculated column dynamically change the rankx to be a different weeknumber because that’s the date they filtered?

[v-huijiey-msft]

Hi @dutch070 ,

Thanks for the reply from parry2k .

Here is the example data I created from the screenshot you provided:

Date	Score
4/1/2024
4/8/2024
4/15/2024
4/22/2024	2
4/29/2024
5/6/2024	3
5/13/2024
5/22/2024
5/27/2024

Create a calculated column that extracts Weekknum:

WeekNumber = WEEKNUM('Table'[Date])

Create a calculated column for sorting:

Rank = RANKX(
    FILTER(
        'Table'.
        'Table'[Date] >= DATE(2024,4,1)
    ),
    'Table'[WeekNumber], . 
    , 
    ASC
)

The page visualization is as follows:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[dutch070]

Yang, thanks for the reply. 

Unfortunabtly your sample data is a little too perfect for my situation. In my situation I already have a calendar calculated table that has all dates in 2024. I updated the table you provided to me it would look like this:

Date	WeekNum	Score
4/1/2024	14
4/2/2024	14
4/3/2024	14
4/4/2024	14	2
4/5/2024	14
4/6/2024	14	3
4/7/2024	14
4/8/2024	15
4/9/2024	15

Actual Screenshot

So in your scenario i would get ranking of every date but not every unique WeekNumber that between my slicer filter. 

Date	WeekNum	Rank
4/1/2024	14	1
4/2/2024	14	2
4/3/2024	14	3
4/4/2024	14	4
4/5/2024	14	5
4/6/2024	14	6
4/7/2024	14	7
4/8/2024	15	8
4/9/2024	15	9

I was thinking I need to create a separate calculated table with only the Distinct week numbers affected and then create a relationship to this new table to my calendar table but I dont know how to make that calculated table dynamic based on my date slicer filter.

WeekNum	Rank
14	1
15	2
16	3
17	4
18	5

[v-huijiey-msft]

Hi @dutch070 ,

After extracting the week of year from the Calendar table through a calculated column, create a calculated column to sort the data on April 1:

Rank =
var _rk=RANKX(
     FILTER(
         'Calendar',
         'Calendar'[Date] >= MIN('Table'[Date])
     ),
     CALCULATE(MIN('Calendar'[WeekNumber])),
     ,
     ASC,Dense
)
return
IF([Date]<MIN('Table'[Date]),BLANK(),_rk)

Create a calculated column to sort the main table by date:

RankNum = RANKX(
     FILTER(
         'Table',
         'Table'[Date] >= MIN('Table'[Date])
     ),
     'Table'[Date],
     ,
     ASC,Dense
)

Drag the Calendar's Rank to the Slicer to filter the year and week, and drag the Main Table's Score and RankNum to the Stacked column chart.

Make the following settings:

The page effect is as follows:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[dutch070]

Once again, your information is getting me closer and helpful but Its still not giving me what I need. Your pbix file has the hard coded the date range as opposed to the date range being driven off a slicer thats filtering my 'calendar' table.  The closest I have got to Is the following code:

Rank:
Rank = RANKX(
    FILTER(
        'Calendar',
        'Calendar'[Date] >= DATE(2024,1,1)
    ),
    'Calendar'[WeekNumber],
    , 
    ASC
)

 But once again the DATE above is hard coded and I need it to start on my filtered date. So I add a CALCULATE in there, 

Rank:
Rank = RANKX(
    FILTER(
        'Calendar',
        'Calendar'[Date] >= Calculate(MIN('calendar'[Date],All('Calendar'[date])),
    ),
    'Calendar'[WeekNumber],
    , 
    ASC
)

But it ALWAYS starts the rankx at the Min date of the whole Calendar and not after my slicer filter. 

[parry2k]

@dutch070 on the formatting pane, change the x-axis type to categorical, it is probably set to continuous.

[dutch070]

Now its really wacky, I need it to be 1,2,3,4,5, etc and in correct order. 

[parry2k]

@dutch070 click on 3 dots on top right corner and make sure the sort column and sort order is correct. Seems like it is sorted by value not the column on the x-axis

[dutch070]

Okay you were right it was and it looks better, but thats still not what I need. Im looking for the week number of the competition. So instead it the X axis Weeknumber starting at 14 (Because my competition start date is April 1st which is the 14th week) I want it to be 1 because its the first week of the competition. Does that make sence?