IF / AND Measure using Values from Two Tables to Display a Measure Value

[novotnajk]

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!!??!??!!?!?

[v-rzhou-msft · Accepted solution]

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. 

[vanessafvg]

Hi are you able to provide some dummy data?

[novotnajk]

Thank you! This worked!  I just replaced the values of 1, 2, etc. with the other measures I created. 

Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[novotnajk]

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.

[novotnajk]

Hi Vanessa, Are you still able to assist? @vanessafvg 

[vanessafvg]

hi thanks for that measures code, are you able to provide some data with this please?