Request for Assistance with Power BI DAX Logic

[bchappidi]

Hi Everyone,

I’m currently working on a Power BI requirement where I need help creating a DAX calculation.

The goal is to create a dynamic rank that resets based on matching effective dates from a reference table. Here's the logic:

• The first block (starting from the minimum date based on "End date" slicer from disconneted table, "period" slicer) should have the rank sequence: 0, 1, 2, 3, etc.

• When a match with an effective date (from the Ref Data table) is found, the rank should reset and start again at 1, 2, 3, etc.

• Also, I would like to carry over the previous last value to use in calculations for the new block.

Ref Data -Example:

Ex: Reference Data:

Expected Output:

I've created calculated columns to:

• Identify the blocks

• Generate ranks within each block

• Reset the ranks based on matching effective dates

However, I’m now facing challenges where I need the ranks to dynamically adjust based on slicer selections (like "End Date"(From disconnted date table, based on user slelected End date, visual should show last 1, yr, 3yr, 5 yr etc) and "Period"), and I’m struggling to pass slicer values properly into the calculated columns ( I knew we can't pass slicer value into cal column but i want to capture slicer value and pass into my caluculation to get the desired result). I would greatly appreciate any guidance on how to achieve the expected output while considering slicer selections.

Example scenario:

• Fact/Main Table: Manager Name = Test1, data from Jan 2019 to Current Date.

• Ref Data Table: Contains effective dates for managers like Test1.

• Date table ( x axis - I considered date from date table and it is joined to Fact table based on date)
• Disconnect date table ( for End date slicer,  based on user slelected End date, visual should show last 1, yr, 3yr, 5 yr etc)
• Period Static table

• Expected Output: Rank should behave as mentioned above, dynamically adjusting to slicer changes.

To make it easier to understand, I’ve also attached the source Excel file and a sample Power BI file showing the structure.

Any guidance, ideas, or better approaches would be truly appreciated!

Thank you so much for your time and support 

[bchappidi86 · Accepted solution]

Hi All,

Thank you so much for your support and insights. I'm glad to let you know that I’ve managed to resolve the issue on my own.

I'm not sure how to close this ticket. Please let me know if there's any action needed from my end.

Regards,

Bharath Ch

[v-achippa]

Hi @bchappidi86,

Thank you for the response, and confirming that the issue is resolved now. I request you to please post the solution here and kindly mark that post as Accept as Solution, so that other community members will find it more easily and the ticket will be closed.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @bchappidi,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @danextian for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? or let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[bchappidi86]

Thank you @danextian for your response.

Please find the link to the mock-up PBIX file (smple data) here:

https://drive.google.com/drive/folders/1qbvZrWe8ttkfrmFGZI0m_exITFin8LtT?usp=sharing

I was able to get the final output ("1 Std Final") using calculated columns, but it is currently working only when the Period slicer is set to "Since Inception."

Here is the breakdown of my caluculations:

1. Segment ID = > Calculates a "segment ID" based on the matching effective dates found in the Ref Data table for each Manager, incrementing the segment count up to the current date

2. SegmentID_FillDown =>  generates a Segment ID for each row by filling down the last known segment based on the current date and Manager, ensuring that previous segment values are carried forward until a new segment is encountered

3. Rank Pre cal =>calculates the rank within a segment for each Manager based on the date, where the rank is adjusted by subtracting 1. The ranking is done in ascending order, and the rank is calculated only for rows within the same segment and Manager

4. Rank Final => calculates the final rank for each Manager by adjusting the rank based on the segment. It checks if there is more than one segment and, if so, increments the rank for subsequent segments while keeping the original rank for the first segment. If there's only one segment, the original rank is retained.

5. 1 STD Pre Cal =>  calculates the 1 Standard Deviation by multiplying the Tracking Error by the square root of the Final Rank.

6. 1 std-PrevSeg-Value => It is designed to retrieve the "1 Standard Deviation value" from the previous segment for a specific manager. It follows these steps:

1. Find the Last Segment: It calculates the previous segment based on the "SegmentID_FillDown"

    for the current row.

2. Get Last Rank of Previous Segment: It retrieves the last rank for the previous segment.

3. Retrieve the Previous Segment’s Last 1 Standard Deviation Value: Finally, it returns the 1 Standard Deviation value of the previous segment, using the previous segment's last rank to identify the exact row.

7. 1 STD Final => 

It calculates the Final 1 Standard Deviation value for a specific manager based on the number of segments available:

1. Check the Number of Segments: It first calculates the total number of distinct segments (SegmentID_FillDown) for the current manager. If there's only one segment (seg_count = 1), it simply returns the "1 std Pre Cal" value for that segment.

2. For Multiple Segments: If there are multiple segments, it adds the "1 std Pre Cal" value of the current segment with the "1 std-PrevSeg-Value" (which is the value from the previous segment). This effectively carries forward the value from previous segments to the current one.

When selecting other periods like 1 year, 3 years, or 5 years, it is not behaving as expected.

Specifically, the Rank Pre and Rank Final calculations are not dynamically adjusting based on slicer selections (such as "End Date" and "Period").

For example, if the user selects an End Date of 31-Mar-2025 and a Period of 1 year, the visual correctly filters the data to include only Mar 2024 to Mar 2025. However, for these filtered dates/months, the rank is not generating correctly (as shown in the below screenshot — ranks are starting from 29, 30, 31, etc., which is incorrect).

Because of this issue, the 1 Std Final values are also not calculating correctly.

How can I make sure that my "rank" calculations and "1 STD Final" values adjust dynamically based on slicer selections, specifically for the End Date and Period slicers?

Any guidance or suggestions would be greatly appreciated!

Thanks in advance for your help.

[danextian]

Hi @bchappidi 

It would be easier for anyone to help you had you posted a workable sample data. Please share link to a sanitized (non-confidential/anonymized/mocked-up) copy of the pbix stored in the cloud.