Forum Discussion

Alta88's avatar
Alta88
Helper IV
3 years ago
Solved

Nested IF Statements

I'm trying to create flags for various pricing levels on our accounts.   So far they're in separate IF statements in this rough logic:   IF Monthly Fee = $45.99 then Monitoring Plan = Standard, ...
Need Help
  • ddpl's avatar
    ddpl
    3 years ago

    Alta88  try this

    First Create 2 table as per below

    Then put monthly fee column in filter slicer

    now create one measure as per below

    Result = var _s = SELECTEDVALUE(Fee[Monthly Fee])

    return
    IF(HASONEVALUE('Table'[Deatils]),
    IF(_s = 45.99,
    IF(SELECTEDVALUE('Table'[Details]) = "Activation Fee", "89.99",
    IF(SELECTEDVALUE('Table'[Details]) = "Monitoring Plan", "Standard",
    IF(SELECTEDVALUE('Table'[Details]) = "Monitored By", "DirectTV",
    IF(SELECTEDVALUE('Table'[Details]) = "Interactive Service Providers", "ATT&T(DTV)",
    IF(SELECTEDVALUE('Table'[Details]) = "Warranty Term", "DTV Qolsys"))))),

    IF(_s = 55.99,
    IF(SELECTEDVALUE('Table'[Details]) = "Activation Fee", "89.99",
    IF(SELECTEDVALUE('Table'[Details]) = "Monitoring Plan", "Premium",
    IF(SELECTEDVALUE('Table'[Details]) = "Monitored By", "DirectTV",
    IF(SELECTEDVALUE('Table'[Details]) = "Interactive Service Providers", "ATT&T(DTV)",
    IF(SELECTEDVALUE('Table'[Details]) = "Warranty Term", "DTV Qolsys"))))),

    IF(_s = 63.99,
    IF(SELECTEDVALUE('Table'[Details]) = "Activation Fee", "129",
    IF(SELECTEDVALUE('Table'[Details]) = "Monitoring Plan", "Platinum",
    IF(SELECTEDVALUE('Table'[Details]) = "Monitored By", "DirectTV",
    IF(SELECTEDVALUE('Table'[Details]) = "Interactive Service Providers", "ATT&T(DTV)",
    IF(SELECTEDVALUE('Table'[Details]) = "Warranty Term", "DTV Qolsys"))))), "Please Select Plan"))))

    Finally ceate table into visualisation as per below:

     

     

     

ddpl's avatar
ddpl
Solution Sage
3 years ago

Alta88  try this

First Create 2 table as per below

Then put monthly fee column in filter slicer

now create one measure as per below

Result = var _s = SELECTEDVALUE(Fee[Monthly Fee])

return
IF(HASONEVALUE('Table'[Deatils]),
IF(_s = 45.99,
IF(SELECTEDVALUE('Table'[Details]) = "Activation Fee", "89.99",
IF(SELECTEDVALUE('Table'[Details]) = "Monitoring Plan", "Standard",
IF(SELECTEDVALUE('Table'[Details]) = "Monitored By", "DirectTV",
IF(SELECTEDVALUE('Table'[Details]) = "Interactive Service Providers", "ATT&T(DTV)",
IF(SELECTEDVALUE('Table'[Details]) = "Warranty Term", "DTV Qolsys"))))),

IF(_s = 55.99,
IF(SELECTEDVALUE('Table'[Details]) = "Activation Fee", "89.99",
IF(SELECTEDVALUE('Table'[Details]) = "Monitoring Plan", "Premium",
IF(SELECTEDVALUE('Table'[Details]) = "Monitored By", "DirectTV",
IF(SELECTEDVALUE('Table'[Details]) = "Interactive Service Providers", "ATT&T(DTV)",
IF(SELECTEDVALUE('Table'[Details]) = "Warranty Term", "DTV Qolsys"))))),

IF(_s = 63.99,
IF(SELECTEDVALUE('Table'[Details]) = "Activation Fee", "129",
IF(SELECTEDVALUE('Table'[Details]) = "Monitoring Plan", "Platinum",
IF(SELECTEDVALUE('Table'[Details]) = "Monitored By", "DirectTV",
IF(SELECTEDVALUE('Table'[Details]) = "Interactive Service Providers", "ATT&T(DTV)",
IF(SELECTEDVALUE('Table'[Details]) = "Warranty Term", "DTV Qolsys"))))), "Please Select Plan"))))

Finally ceate table into visualisation as per below: