Placing A Cap on A Value for a Date Period

[spartan27244]

I have a need for a user to provide a CAP value for What-If analysis.

Table1: 'pbi vwCustomerReportPeriods' - contains time periods the customer uses for reporting purposes. Benefit Year, Calendar Year, Quarter. So there would be a Period defined as 2016 - CY, 2017 - CY, etc, with start and ends dates defined at the beginning and end of the year. The table contains a date for every day within those periods.

Table2: 'pbi PMD' - An eligibility table. It has a row entry for each month a person has med insurance. it is related to Table1 via a calculated column called 'PBI_RptPeriodKey'. So a slicer selection on Table 1 for 2016 - CY would filter to only the rows in this table for that period of time. 

Table3: 'pbi vwMedClaims' - A table that represents medical claims for each person. This table contains a Value MedPlanPaid which is the one I need to apply the Financial Cap. This table is related to Table2 by a calculated column 'PBI-ClaimKey'

The cap value is stored as a What-If parameter 'PlanPaidCap'.

The goal is to apply the cap based on an individual's Sum of MedPlanPaid for the selecgted period. For example, user selects time period 2019 - CY, and the Cap Value is set to 150,000. If an individual's Sum of MedPlanPaid is >= the Cap i.e. 150,000, then the cap value will be used as their sum amount not the actual sum, if it's < the cap then the actual summed value will be used. 

Person	Sum Value	Value to Use
person b	275,000	150,000
person c	90,000	90,000
person d	45,000	45,000
person e	157,000	150,000

The desired Sum for the time period should reflect 150,000+ 90,000 + 45,000 + 150,000

The realtional model looks like so...

How can I create a function to do this? one thought was to use a function to create a table that represented the total sum for each person per period of time, and then add a claculated column to apply the what if value, and then sum that column. But really at a loss as to where to start.

[spartan27244 · Accepted solution]

MedPlanPaidCap =
var Cap = SELECTEDVALUE('FinCap'[Value], 0)

var P = SUMMARIZE('Med Claims', 'Med Claims'[Person_DVK], "PP", Sum('Med Claims'[MedPlanPaid]))

Var Z = SUMX(P, If(Cap = 0, [PP], If([PP] > Cap, Cap, [PP])))

return z

[Anonymous]

Hi @spartan27244 

I would first focus on the model as it doesn't look good enough to me. Please read this first and then create a good model following Best Practices. Also, I'd strongly but kindly suggest that you name attributes and tables in a user-friendly manner, hide the ID fields on which you join your tables and make sure you don't have unnecessary many-to-many or cross-filtering enabled. Fact tables must always be hidden and slicing can take place only via dimensions. Please tidy up your model if you want to stay away from troubles later down the line.

You should also watch this: Data modeling best practices - Part 1 - in Power BI and Analysis Services - YouTube

If you get your model to a tidy state, I'll try to write the measure for you. Hope this works 🙂

[spartan27244]

The only many to many relation ships exist beween the date table and related date transaction tables. I know of no what to avoid this. And this model is about as star as it gets for the type of data. some tables can be flatted out but they are not an issue for the moment, the calculation metioned above is.

[Anonymous]

"The only many to many relation ships exist beween the date table and related date transaction tables."

May I please ask why? This setup immediately tells me something's fishy might be going on here. I've never seen a correct model in my life (and I've seen many) where one had a date table connected to a fact table with a many-to-many relationship (apart from models where there was a granularity issue, of course). The many-to-many relationship exists in PBI to solve one very specific problem: the problem of granularity. Do you have such a problem between your date table and the fact tables? In other words, do you have fact tables that are on a lower granularity than your date table?

Also, bi-directional filtering is a very dangerous thing and should be used with utmost care since it can make a model ambiguous and you won't be able to explain how DAX has arrived at the figures it has. Again, bi-directional filtering exists in PBI to solve a very specific problem: the problem of a true many-to-many relationship. Such a relationship is present in a model only when there are 2 dimensions for which there must exist a bridge table that links them (think Accounts and Customers, where a customer can have several accounts and one account can be shared by many customers).

[spartan27244]

You can take alook at this post, which is where I am addressing the date issue.

Mutiple date replatioships or reporting periods - Microsoft Power BI Community