What I'm trying to achieve.
User selects a period from a slicer.
I want a table (visual) to show the actions due in the selected period.
I want a another table (visual) to show the actions due in the next period.
I want a third table (visual) to show the actions due the period after next.
My model looks like this
The ABP table is as below
(RSPDuePeriod and DueIndex are both merged from the DimDate & DimPeriod Tables in power query)
DimDate is a date table with each date assigned to the relevant period
DimPeriod is a summary of DimDate but with just the periods so I can index each period for the next period etc
SELECTEDVALUE(DimPeriod[Index]) +1 or +2 to get the relevant index but I can't then work out how to make this filter the information in the ABPDeliverables table? or if this is even the right approach
I've tried creating a table using SELECTEDVALUE but it appears the tables don't refresh for the value in a slicer.
Any help much appreciated - if I need to tweak the model or date tables I can but this feels like it's simple I just can't seem to make it work.
Select RS_YEARPERIOD 2401 (which equates to index 2)
Visual 1 shows rows from ABPDeliverables which have an RSPDuePeriod of 2401 (Index 2)
Visual 2 shows rows from ABPDeliverables which have an RSPDuePeriod of 2402 (Index 3)
Visual 3 shows rows from ABPDeliverables which have an RSPDuePeriod of 2403 (Index 4)
I will then want to add in a calculated measure to show a RAG status to each deliverables based on whether the due date is overdue or with X days from today - but that is secondary to showing the three tables.
Solved! Go to Solution.
You don't need DimPeriod. It makes your setup more complex. Merge it into DimDate.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
Please show the expected outcome based on the sample data you provided.
@lbendlin thanks for the reply.
This is a link to a sample file that contains anonymised data of each table and then a sheet with the desired outcome - hopefully the expected outcome is clear.
We Transfer link.
DimPeriod Table can easily be removed as I only created (by duplicating DimDate) it so I could get a unique list of Periods and apply an index. The plan here was that this meant I could just go with SELECTEDVALUE +1 and then +2 but needless to say that didn't work.
@lbendlin fanatastic thank you - this worked perfectly.
For anyone else coming across this - note that the relationship between the two tables is inactive.
Makes sense when you think about it but took me a minute to figure that out why the next and next after tables were coming in blank despite the measures working.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.