none
Unable to add a Calculated field with IF condition - Needed it in my Power View charts

    Question

  • I am trying to add a calculated field which is having an IF condition. I read that such fields are called Explicit calculated fields and can be used in Power View as well. I am using the below formula:

    =IF([Ratio]>400000,"1",IF([Ratio]>2000,"2","3"))

    Where "Ratio" is another calculated field

    My IF condition formula is correct, I know that and I can see the value in the calculated field as well. Now, the problem is that I cant see it in My 'Power View Fields' Section. I also created two more calculated fields which are just the Sum/Count of columns, but this manual formula field is not visible there. 

    Is the IF condition creating a problem, or the calculated field used within the formula is creating a prob or anything else?

    Please help me.

    Wednesday, March 12, 2014 10:04 AM

Answers

  • Hi Ankit,

    Perhaps you can try the following approach...

    I've created a tool table called 'Band'. It will not have any relationships defined and we will handle it's behaviour with some DAX. 

    BandID

    BandName

    1

    High

    2

    Medium

    3

    Low

    I have created 5 calculated fields (i.e. measures) against this table:

    CountryMaxRatio

    CountryMaxRatio:=
    MAXX(
      VALUES(Country[CountryName]), 
      [Ratio]
    )

    CountryHigh

    CountryHigh:=
    CALCULATE(
      [CountryMaxRatio], 
      FILTER(
        VALUES(Country[CountryName]), 
        [Ratio]  > 400000)
    )

    This measure will only return values for countries that have a Ratio above 400,000. The value returned will belong to the country with the worst (i.e. MAX) ratio out of all the qualifying countries.

    CountryMedium

    CountryMedium:=
    CALCULATE(
      [CountryMaxRatio], 
      FILTER(
        VALUES(Country[CountryName]), 
        [Ratio]  > 200000 && [Ratio] <400000)
    )

    This measure will only return values for countries that have a Ratio that is greater than 200,000 and than less 400,000. The value returned will belong to the country with the worst (i.e. MAX) ratio out of all the qualifying countries.

    CountryLow

    CountryLow:=
    CALCULATE(
      [CountryMaxRatio], 
      FILTER(
        VALUES(Country[CountryName]), 
        [Ratio]  <= 200000)
    )

    This measure will only return values for countries that have a Ratio of 200,000 or less. The value returned will belong to the country with the worst (i.e. MAX) ratio out of all the qualifying countries.

    WorstRatio

    WorstRatio:=
    IF(
      HASONEVALUE(Band[BandName]),
      SWITCH(
        VALUES(Band[BandName]), 
        "High", [CountryHigh], 
        "Medium", [CountryMedium], 
        "Low", [CountryLow]
      )
    )

    This measure checks first that there is a single value in the 'BandName' column in the current evaluation context. If there are multiple values then it will return nothing i.e. a blank value. If there is a single value in this column then it will use this value to determine which measure value to return. E.g. If the value is "High" then the [CountryHigh] measure value will be returned.

    I created a Power View map visualisation where I've placed 'WorstRatio' in the Size field, 'CountryName' in the Location field, and 'BandName' in the Color field...

    We get the yellow warning message about the missing relationship. You can ignore this message in this instance because we purposely did not create a relationship.

    Earlier, when describing the CountryHigh, CountryMedium, and CountryLow measures I mentioned that the worst ratio would be returned out of the qualifying countries. Since we are using the 'CountryName' column in Power View's Location field, this means that the calculation will be evaluated within the context of a single country so the WorstRatio will simply return the country's ratio.

    Due to current limitations in Power View, we can't pick the exact colours used for the legend (red for 'Medium' isn't very intuitive), but we can at least see the name/description of each group or band that each country is classified under.

    Note: I've used country names in my test set-up because it allowed me to quickly create the test data, but you can apply the same approach with other types of location data. I have also used different band ranges (e.g. medium is > 200,000 and < 400,000 instead of > 2,000 and < 400,000) so that the example would show at least 1 country per band in the Power View map :)

    Edit: Here is the test data that was used...

    CountryID CountryName
    1 United Kingdom
    2 Germany
    3 USA
    4 Spain
    5 Iceland

    TotalPopulation CountryID
    1610642 1
    1681956 2
    1076958 2
    505650 3
    1355120 3
    784730 3
    103890 4
    1166790 4
    32404 5

    Item ItemName CountryID
    2 Item 2 1
    4 Item 4 2
    2 Item 2 2
    1 Item 1 3
    3 Item 3 3
    3 Item 3 3
    4 Item 4 4
    16 Item 16 4
    1 Item 1 5



    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog

    Tuesday, March 25, 2014 12:25 PM
    Moderator

All replies

  • Where are you creating these calculated fields? Inside tabular model cube or inside powerpivot model?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 12, 2014 10:08 AM
  • I am trying to create it in PowerPivot Model.
    Wednesday, March 12, 2014 11:01 AM
  • Can you fisrt check and see if columns are available inside pivot tables/charts by adding a table/chart inside excel powerpivot?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 12, 2014 11:06 AM
  • Also check these out

    http://www.solidq.com/sqj/Pages/Business_Intelligence/Building-Ideal-PowerPivot-Model-for-Power-View-reports.aspx

    http://technet.microsoft.com/en-us/library/hh759324.aspx


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 12, 2014 11:16 AM
  • Hi Ankit,

    This is an issue with Power View which only surfaces measures that return numeric data types. Take a look at this Power View thread: Calculated Field formula not appearing in Power View Field List panel


    In your case, if you actually want to return numeric values from your IF logic than you can change the formula to the following:

    IF(
      [Ratio] > 400000,
      1,
      IF(
        [Ratio] > 2000,
        2,
        3
      )
    )


    Note that I have simply removed the double quotes.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog

    Thursday, March 13, 2014 9:49 AM
    Moderator
  • Thanks, I tried this and was successfully able to add this to power View tables.

    But, I am still not able to add this calculated field on some visualization i.e. Map, etc.

    When I drag the field to table, it is being added, but as soon as I turn the visualization to geospatial map and drag this ratio field to "SIZE", it hangs up. Also, are we not allowed to drag such calculated fields to "COLOR", etc.

    Thursday, March 13, 2014 11:20 AM
  • Only columns can be placed in the 'Color' section (a colour will be picked per distinct value in a column).

    The hanging issue is performance related so I will need to know a bit more about your model to help with this. I'd like to get a better idea of what you're trying to achieve and what your data looks like. Are you able to provide the following details:

    • The table columns and a very small, representative data sample (i.e. test/dummy data) which can be used to test any solutions
    • The relationships between the tables
    • How you have defined your [Ratio] calculated field and any other calculated fields that are relevant to this problem area

    Please provide any other details that you think may give a clearer understanding of your model and this issue.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog

    Thursday, March 13, 2014 12:03 PM
    Moderator
  • Here we go,

    In total we have three calculated fields:

    1. Total Items:=SUM([Item])

    2. Total Population:=SUM([total_population])

    3. Ratio:= [Total Population]/[Total Items]

    I have in total three tables, one for item details, another for population, and last one is used for making relationship between first two tables based on their 'County Name'. Third table just contains unique county names. 

    Now, what I need is, I want to create a Power View visualization which can help me classify the items on a MAP based on a calculation i.e. =IF([Ratio]>400000,1,IF([Ratio]>2000,2,3))

    There would be three categories, 1, 2 and 3 and Color would be based on these three only. Item 1 needs to be reviewed as it is high priority and 2 with less priority and 3 with least prio.

    I have then further created a KPI and got my prob resolved, but I would like to map such thing on a MAP instead of a simple table.

    Thursday, March 13, 2014 12:24 PM
  • Hi Ankit,

    Perhaps you can try the following approach...

    I've created a tool table called 'Band'. It will not have any relationships defined and we will handle it's behaviour with some DAX. 

    BandID

    BandName

    1

    High

    2

    Medium

    3

    Low

    I have created 5 calculated fields (i.e. measures) against this table:

    CountryMaxRatio

    CountryMaxRatio:=
    MAXX(
      VALUES(Country[CountryName]), 
      [Ratio]
    )

    CountryHigh

    CountryHigh:=
    CALCULATE(
      [CountryMaxRatio], 
      FILTER(
        VALUES(Country[CountryName]), 
        [Ratio]  > 400000)
    )

    This measure will only return values for countries that have a Ratio above 400,000. The value returned will belong to the country with the worst (i.e. MAX) ratio out of all the qualifying countries.

    CountryMedium

    CountryMedium:=
    CALCULATE(
      [CountryMaxRatio], 
      FILTER(
        VALUES(Country[CountryName]), 
        [Ratio]  > 200000 && [Ratio] <400000)
    )

    This measure will only return values for countries that have a Ratio that is greater than 200,000 and than less 400,000. The value returned will belong to the country with the worst (i.e. MAX) ratio out of all the qualifying countries.

    CountryLow

    CountryLow:=
    CALCULATE(
      [CountryMaxRatio], 
      FILTER(
        VALUES(Country[CountryName]), 
        [Ratio]  <= 200000)
    )

    This measure will only return values for countries that have a Ratio of 200,000 or less. The value returned will belong to the country with the worst (i.e. MAX) ratio out of all the qualifying countries.

    WorstRatio

    WorstRatio:=
    IF(
      HASONEVALUE(Band[BandName]),
      SWITCH(
        VALUES(Band[BandName]), 
        "High", [CountryHigh], 
        "Medium", [CountryMedium], 
        "Low", [CountryLow]
      )
    )

    This measure checks first that there is a single value in the 'BandName' column in the current evaluation context. If there are multiple values then it will return nothing i.e. a blank value. If there is a single value in this column then it will use this value to determine which measure value to return. E.g. If the value is "High" then the [CountryHigh] measure value will be returned.

    I created a Power View map visualisation where I've placed 'WorstRatio' in the Size field, 'CountryName' in the Location field, and 'BandName' in the Color field...

    We get the yellow warning message about the missing relationship. You can ignore this message in this instance because we purposely did not create a relationship.

    Earlier, when describing the CountryHigh, CountryMedium, and CountryLow measures I mentioned that the worst ratio would be returned out of the qualifying countries. Since we are using the 'CountryName' column in Power View's Location field, this means that the calculation will be evaluated within the context of a single country so the WorstRatio will simply return the country's ratio.

    Due to current limitations in Power View, we can't pick the exact colours used for the legend (red for 'Medium' isn't very intuitive), but we can at least see the name/description of each group or band that each country is classified under.

    Note: I've used country names in my test set-up because it allowed me to quickly create the test data, but you can apply the same approach with other types of location data. I have also used different band ranges (e.g. medium is > 200,000 and < 400,000 instead of > 2,000 and < 400,000) so that the example would show at least 1 country per band in the Power View map :)

    Edit: Here is the test data that was used...

    CountryID CountryName
    1 United Kingdom
    2 Germany
    3 USA
    4 Spain
    5 Iceland

    TotalPopulation CountryID
    1610642 1
    1681956 2
    1076958 2
    505650 3
    1355120 3
    784730 3
    103890 4
    1166790 4
    32404 5

    Item ItemName CountryID
    2 Item 2 1
    4 Item 4 2
    2 Item 2 2
    1 Item 1 3
    3 Item 3 3
    3 Item 3 3
    4 Item 4 4
    16 Item 16 4
    1 Item 1 5



    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog

    Tuesday, March 25, 2014 12:25 PM
    Moderator
  • Michael, this would be content for another Wiki article!

    http://social.technet.microsoft.com/wiki/contents/articles/23330.technet-guru-contributions-for-march.aspx#SQL_BI_amp_Power_BI

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, March 27, 2014 5:37 AM
    Owner