Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

IF statement excluding blanks for new column

I am trying to do a count of email by the selected markets and those outside of these markets will be given 'All Other Markets' as a value.

 

'Leads' table is connected to to the 'Markets - MSA' table by 1 to many (by Zip Code) 

 

'All Other Markets' should equAL 966 (876 + 90) Why arent those 90 EmailAddress being grouped with 'ALL OTHER MARKETS'?

 

I see that those 90 records Zip Codes arent associated with a CBSA but shouldnt they be grouped regardless since i am calling out those 9 CBSA's and placing the rest in another group. 

 

Please help me understand if i am doing something wrongALL OTHER MARKETS should equal 966 (876 +90)

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Anonymous ,

     

    The blank value is because the relational column does not match.

     

    We suggest you can create the Target market column in 'Markets - MSA' table.

    The result like this,

     

    Column = 
IF(
    [Code]=1||[Code]=2||[Code]=3||[Code]=4||[Code]=5||[Code]=6,
    CALCULATE(MAX('Leads'[CBSA name]),FILTER('Leads','Leads'[CBSA code]='Markets - MSA'[Code])),
    "ALL Ohters")

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous 

    Can you please provide more info with screenshots, input and required output in table format for the community so that you ask can be better understood and resolved quickly.

    Also, try

    IF(ISBLANK([column]),0,[Column])

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous I want to keep those 9 CBSA Names vs the rest of the CBSA(All Other Markets) but its keeping the blanks from being combined with the 'All other Markets'

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        An easier way to do this would be to use a Group column.  Please see this link.  With it you can group all the others into an All Others categories, keeping the desired ones with same name.

        https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

         

        Otherwise, you'll need to use an IF that if the SELECTEDVALUE() is "All other markets" do a different calculation (probably comparing ALL the values of the fact table to ALL of the values of the dim table.

         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    The blank value is because the relational column does not match.

     

    We suggest you can create the Target market column in 'Markets - MSA' table.

    The result like this,

     

    Column = 
IF(
    [Code]=1||[Code]=2||[Code]=3||[Code]=4||[Code]=5||[Code]=6,
    CALCULATE(MAX('Leads'[CBSA name]),FILTER('Leads','Leads'[CBSA code]='Markets - MSA'[Code])),
    "ALL Ohters")

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.