Forum Discussion
IF / AND Measure using Values from Two Tables to Display a Measure Value
Hi!
I have another tough formula to build (at least I think it's tough)!
I created two tables in my model called, "Plan Spend Category", and "Plan Spend Parameter" - These two tables, identify the following:
Plan Spend Category
Medical = Medical Plan
Rx = Pharmacy Plan
Total = Both Medical and Pharmacy
Plan Spend Parameter
Amt Paid = Total Paid by the Company
PEPM = Per Employee Per Month
PEPY = Per Employee Per Year
PMPM = Per Member Per Month
PMPY = Per Member Per Year
In my model I have several measures:
Medical PEPM
Medical PMPM
Rx PEPM
Rx PMPM
Total PEPM
Total PMPM
Medical PEPY
Medical PMPY
Rx PEPY
Rx PMPY
Total PEPY
Total PMPY
Each of these measures is a formula to display a dollar value.
My issue is, how do I create a formula that says, if a filtered value from Plan Spend Category and Plan Spend Parameter is "X", show "X Measure (i.e., if Plan Spend Category is Medical and Plan Spend Parameter is PEPM, then show value for Medical PEPM, etc., etc.. If no filter is selected, the default is Total Amt Paid.
Help!!??!??!!?!?
Hi novotnajk
I think you want to show different results (measures) by select slicer.
Firstly you need to build two slicer table.
Category Table:
Parameter Table:
Due to I don't know your value, I build a sample measure for you. I use number to replace the measures you use.
Measure = VAR _Selcategory = SELECTEDVALUE ( 'Plan Spend Category'[Category] ) VAR _Selparameter = SELECTEDVALUE ( 'Plan Spend Parameter'[Parameter] ) VAR _Medical_PEPM = 1 VAR _Medical_PMPM = 2 VAR _Rx_PEPM = 3 VAR _Rx_PMPM = 4 VAR _Total_Amt_Paid = 5 RETURN IF ( ISFILTERED ( 'Plan Spend Category'[Category] ) && ISFILTERED ( 'Plan Spend Parameter'[Parameter] ), IF ( AND ( _Selcategory = "Medical", _Selparameter = "PEPM" ), _Medical_PEPM, IF ( AND ( _Selcategory = "Medical", _Selparameter = "PMPM" ), _Medical_PMPM, IF ( AND ( _Selcategory = "Rx", _Selparameter = "PEPM" ), _Rx_PEPM, _Rx_PMPM ) ) ), _Total_Amt_Paid )Result is as below.
As default it will show the result of _Total_Amt_Paid.
If I select Medical and PEPM in Slicers, it will show the result of _Medical_PEPM.
You can add more measures by var and update this measure like I show you above.
You can download the pbix file from this link: IF / AND Measure using Values from Two Tables to Display a Measure Value
If this reply still couldn't help you solve your problem, please show me more details about your data model.
You can give me a sample table like you are dealing with, show me a screenshot of your data model or you can provide me with your pbix file by your Onedrive for business.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- Hi are you able to provide some dummy data?
Hi!
Sure. Here are the actual table names and data:
Table 1: 'MeasureGrouper[Plan Spend Category] (values of Medical, Rx, Total)
Table 2: 'MeasureGrouper[Plan Spend Paratmenter] (values of PEPM, PEPY, PMPM, PMPY, Amt Paid]
Measures
To get PEPY:
Medical PEPY = CALCULATE(DIVIDE('Medical[Plan Spend]),(Members)
Medical PEPM = CALCULATE(DIVIDE(Medical PEPY), (Months) ---- note, that the months measure takes a look at the min/max dates of a reporting period field and creates a number, which is always equal to 12.
Rx and Total measures look exactly the same, except you replace Medical with Rx or with Total.
Hi Vanessa, Are you still able to assist? vanessafvg
Thank you! This worked! I just replaced the values of 1, 2, etc. with the other measures I created.
Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Hi novotnajk
I think you want to show different results (measures) by select slicer.
Firstly you need to build two slicer table.
Category Table:
Parameter Table:
Due to I don't know your value, I build a sample measure for you. I use number to replace the measures you use.
Measure = VAR _Selcategory = SELECTEDVALUE ( 'Plan Spend Category'[Category] ) VAR _Selparameter = SELECTEDVALUE ( 'Plan Spend Parameter'[Parameter] ) VAR _Medical_PEPM = 1 VAR _Medical_PMPM = 2 VAR _Rx_PEPM = 3 VAR _Rx_PMPM = 4 VAR _Total_Amt_Paid = 5 RETURN IF ( ISFILTERED ( 'Plan Spend Category'[Category] ) && ISFILTERED ( 'Plan Spend Parameter'[Parameter] ), IF ( AND ( _Selcategory = "Medical", _Selparameter = "PEPM" ), _Medical_PEPM, IF ( AND ( _Selcategory = "Medical", _Selparameter = "PMPM" ), _Medical_PMPM, IF ( AND ( _Selcategory = "Rx", _Selparameter = "PEPM" ), _Rx_PEPM, _Rx_PMPM ) ) ), _Total_Amt_Paid )Result is as below.
As default it will show the result of _Total_Amt_Paid.
If I select Medical and PEPM in Slicers, it will show the result of _Medical_PEPM.
You can add more measures by var and update this measure like I show you above.
You can download the pbix file from this link: IF / AND Measure using Values from Two Tables to Display a Measure Value
If this reply still couldn't help you solve your problem, please show me more details about your data model.
You can give me a sample table like you are dealing with, show me a screenshot of your data model or you can provide me with your pbix file by your Onedrive for business.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
