Forum Discussion

admin11's avatar
admin11
Icon for Memorable Member rankMemorable Member
4 years ago
Solved

Sales Table how to remove Other Row from Table ?

Hi All

 I have below expression working fine :-

 

BRAND_P =
IF ( 'SALES'[BRAND_] = Blank(),
"No Brand",if(SALES[BRAND_] in
{"UNIVERSAL"," Universal Robot","Universal Robot"},
'SALES'[BRAND_], "Other"))
 
it will return below table :-

Now my task is to remove the row which display Other . I try to modify the expression to below :-

 

BRAND_P_ =
IF ( 'SALES'[BRAND_] = Blank(),
"No Brand",if(SALES[BRAND_] in
{"UNIVERSAL"," Universal Robot","Universal Robot"}))
 
But I get error msg below  , can some one share with me where i go wrong ?
 
Too few arguments were passed to the IF function. The minimum argument count for the function is 2.
 
Paul
Need Help
  • SivaMani's avatar
    SivaMani
    4 years ago

    admin11 , Is that what you need?

     

    BRAND_P_NEW =
    IF (
    'SALES'[BRAND_] = BLANK (),
    "No Brand",
    IF (
    SALES[BRAND_] IN { "UNIVERSAL", " Universal Robot", "Universal Robot" },
    'SALES'[BRAND_]
    )
    )

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    admin11 , The first one is correct as a new column. if you want to remove others, you need to use a measure or use the page or visual level filter <> other

     

    calculate([sales YTD], filter(Table, Table[BRAND_P_]<> "Other")

     

    Assumed that sales YTD is a measure

    • admin11's avatar
      admin11
      Icon for Memorable Member rankMemorable Member
      4 years ago

      amitchandak SivaMani 

      Thank you both for sharing .

       

      I am sorry , it is my explanation is not clear to all of you.

      what i want is :-

       
      I have below expression , which return  , "UNIVERSAL"," Universal Robot","Universal Robot" sales , and it also return Other sales at row one :-

      BRAND_P =
      IF ( 'SALES'[BRAND_] = Blank(),
      "No Brand",if(SALES[BRAND_] in
      {"UNIVERSAL"," Universal Robot","Universal Robot"},
      'SALES'[BRAND_], "Other"))
       
      My question is , how can i modify the above expression , by remove those i have high lighted script in red , so that the report will not display other sales :-
       
      BRAND_P_NEW =
      IF ( 'SALES'[BRAND_] = Blank(),
      "No Brand",if(SALES[BRAND_] in
      {"UNIVERSAL"," Universal Robot","Universal Robot"},
      'SALES'[BRAND_], "Other"))
       
      Some how when i remove those red highlighted script , i get error,
       
      Paul 
      • SivaMani's avatar
        SivaMani
        Icon for Resident Rockstar rankResident Rockstar
        4 years ago

        admin11 , Is that what you need?

         

        BRAND_P_NEW =
        IF (
        'SALES'[BRAND_] = BLANK (),
        "No Brand",
        IF (
        SALES[BRAND_] IN { "UNIVERSAL", " Universal Robot", "Universal Robot" },
        'SALES'[BRAND_]
        )
        )

  • SivaMani's avatar
    SivaMani
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    admin11, there are a couple of ways to do it.

    1. Add report/page/visual level filter on Brand column(exclude Other)
    2. In the Sales YTD Measure, Add the below filter
    CALCULATE(<YTD Logic>,KEEPFILTERS('SALES'[BRAND_P] <> "Other"))

     

    Hope this helps!