locked
Filtering a text Field with DAX RRS feed

  • Question

  • Hi,

    I am using the below DAX to calculate a column which is Event type. I only interested on "ABCD" ,"EFGH","CSSWW", "ERDSDS" and DSDSADSA, but there is more than ten kind of Event Type. So I use the IF statement to filter these Event type and put "Other" reset of the event type(which does not interest me). Now I come with a problem the How can I remove all "Other" field only display relevant Event type? Just want to clean up my Report even I use Event type in Slicer.

    =IF([eventtype] ="ABCD","ABCD",
    IF([eventtype] ="EFGH","EFGH",
    IF([eventtype] ="CSSWW","CSSWW",
    IF([eventtype] ="ERDSDS","ERDSDS",
    IF([eventtype] ="SDSDSADSA","SDSDSADSA","Other")))))

     

    Thank you in advance !

    Regards

     


    erkindunya
    Monday, November 14, 2011 4:34 PM

Answers

  • Enver,

    As I told, you can easily use a measure to solve your issue. Missing a repro, I tried it with AdventureWorks. If you want a chart of products divided by color, but want to ignore black ones (so that they do not appear on the axis), this measure will work fine:

    CALCULATE (
        COUNTROWS (DimProduct),
        FILTER (VALUES (DimProduct[Color]), DimProduct[Color] <> "Black")
    )

    Clearly, you will need to adapt it to suit your specifi needs, but hopefully the technique should be clear, it is pretty easy indeed, even if it involves the usage of VALUES, which is often not well understood.


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    • Marked as answer by erkindunya2010 Friday, November 18, 2011 2:38 PM
    Friday, November 18, 2011 2:21 PM

All replies

  • Use BLANK() instead of "Other" in your formula.
    Monday, November 14, 2011 7:27 PM
  • Hi David,

    Thank you for your fast reply, I used blank() it return same result, which replaces the "other" with nothing(blank).

    =IF([eventtype] ="ABCD","ABCD",
    IF([eventtype] ="EFGH","EFGH",
    IF([eventtype] ="CSSWW","CSSWW",
    IF([eventtype] ="ERDSDS","ERDSDS",
    IF([eventtype] ="SDSDSADSA","SDSDSADSA",blank())))))

    or

    =IF([eventtype] ="ABCD","ABCD",
    IF([eventtype] ="EFGH","EFGH",
    IF([eventtype] ="CSSWW","CSSWW",
    IF([eventtype] ="ERDSDS","ERDSDS",
    IF([eventtype] ="SDSDSADSA","SDSDSADSA","")))))

     

    What I want is get rid off "other" and blank filter ABCD" ,"EFGH","CSSWW", "ERDSDS" and DSDSADSA even type.

    Regards



    erkindunya
    Monday, November 14, 2011 9:18 PM
  • You can then use a slicer to select all of the fields you want, excluding the blank field.
    Tuesday, November 15, 2011 1:01 AM
  • Hi Erkindunya,

    Besides David's professional advise, you can also click the Category icon at the bottom-left of your chart, then you will see the filter, you can select which category you want to show, you can also make use of the Value Filters or Label Filters option.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, November 16, 2011 9:34 AM
  • Wouldn't it be easier to filter the source data, so to ignore data you are not interested in?

    Anyway, a viable solution would be to create a measure that counts only rows which do not contain "Other" and returns BLANK for "Other", having the BLANK value, the corresponding label should disappear.


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    Wednesday, November 16, 2011 1:35 PM
  • Hi Challen, David, Alberto,

    Thank you very much for your help.

    Alberto- I am reading your Book Alberto, it is very nice, do you have video tutorial for the training or any centre in London do the training for you. I filtered the Data source when I did connection, it works fine what is the filter meaning of  "Applied filter: [eventtype] = N'Closed - Unable to Complete' ". is there any way to convert the PowerPivot data source filter to DAX.

    Regards

    Enver
    erkindunya
    Thursday, November 17, 2011 4:36 PM
  • Thanks Alberto !

    erkindunya
    Thursday, November 17, 2011 4:37 PM
  • Enver,

    I did not understand the issue... can you please be a bit more specific?

    For what concerns the course, we currently don't have a training center in UK but, if you are interested, we are going to deliver the first online course next week. Unfortunately, we will use far east time zone, so it will be late night here in Europe...


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    Thursday, November 17, 2011 9:42 PM
  • Hi Alberto,

    I have to filter string in my PowerPivot, only numeric value is EventID I make it as count on Values. I have event type around 20 of them. One requirement for the report only looking at event type 1 to 5. other one is 6 to 9 and other is reset of the event type. So I have to filter data for best performance I did use filter I use if to filter on my first version for example: =IF([eventtype] ="ABCD","ABCD",
    IF([eventtype] ="EFGH","EFGH",
    IF([eventtype] ="CSSWW","CSSWW",
    IF([eventtype] ="ERDSDS","ERDSDS",
    IF([eventtype] ="SDSDSADSA","SDSDSADSA","")))))

    and make all other event type to blank, when it is blank or replaced with "other event type", the client did not like he ask me remove the other and blank just show relevant event type in each report, So I filtered Data source on Data connection I using "preview and filter" option to filter out Event type. It shows custom filter language "Applied filter: [eventtype] = N'Closed - Unable to Complete' ". Is there any way to convert the PowerPivot data source filter to DAX.

    NOTE:"Closed - Unable to Complete" is one of the event type, I am only filtering that even type.

    Hope it helps. Thank you very much for your time.


    erkindunya
    Friday, November 18, 2011 10:46 AM
  • Enver,

    As I told, you can easily use a measure to solve your issue. Missing a repro, I tried it with AdventureWorks. If you want a chart of products divided by color, but want to ignore black ones (so that they do not appear on the axis), this measure will work fine:

    CALCULATE (
        COUNTROWS (DimProduct),
        FILTER (VALUES (DimProduct[Color]), DimProduct[Color] <> "Black")
    )

    Clearly, you will need to adapt it to suit your specifi needs, but hopefully the technique should be clear, it is pretty easy indeed, even if it involves the usage of VALUES, which is often not well understood.


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    • Marked as answer by erkindunya2010 Friday, November 18, 2011 2:38 PM
    Friday, November 18, 2011 2:21 PM
  • Hi Alberto,

    Thank you very much for your help.


    erkindunya
    Friday, November 18, 2011 2:39 PM