Forum Discussion

kp_wood13's avatar
kp_wood13
Helper I
3 years ago

IF Statement returning partial data

I'm working on a report where I need to show a project and all the associated go-live dates and go-live entities associated with it. Project name is no issue and I've figured out pulling all the dates and listing them in one column in numerical order. The issue I'm running into is matching up the entities with the go-lives. I've attempted the following in a measure (truncated here due to length):

 

Impacted Entites = IF(ISBLANK(SELECTEDVALUE('NIKU ODF_CA_PROJECT'[app_tar_imp_date])),"","1-" & SELECTEDVALUE('NIKU ODF_MULTI_VALUED_LOOKUPS'[bjc_facility1]) & UNICHAR(10)) &
IF(ISBLANK(SELECTEDVALUE('NIKU ODF_CA_PROJECT'[phase2_date])),"","2-" & SELECTEDVALUE('NIKU ODF_MULTI_VALUED_LOOKUPS'[bjc_facility2]) & UNICHAR(10)) &
IF(ISBLANK(SELECTEDVALUE('NIKU ODF_CA_PROJECT'[phase3_date])),"","3-" & SELECTEDVALUE('NIKU ODF_MULTI_VALUED_LOOKUPS'[bjc_facility3]) & UNICHAR(10))

 

Which results in 

Rather than returning the specific entities for each go-live, it returns nothing in the measure I created unless I add one the specific entity columns but then it pulls every possibility. Any suggestions? Let me know what else I can provide.

Need Help
Tutorial Requests

2 Replies

  • Alex_Sawdo's avatar
    Alex_Sawdo
    Resolver II
    3 years ago

    Is there possibly a better way of determining which entities are attached to which go-live? From what you've shared so far with the example, would a new table be able to accomplish your goal? Basically, you'd have one table with your go-lives and another table that contains each combination of go-live and entity. I can't say much more unless you share more details about your data model/report. 

    • kp_wood13's avatar
      kp_wood13
      Helper I
      3 years ago

      Possibly, what I'm working with is a project table that has all the dates and other relevant project data and another table that contains the lookup values for the entities. Initially it had an ATTRIBUTES column and a VALUES column that contained the facility name and entity name respectively. I pivoted them to create facility columns populated with the entity (below). My attempt has been to match the first date with the bjc_facility1 value but I think having multiple values may be part of my problem.