How do I have Power BI return text items that are not in a list? Assuming I add a column

[datadelta]

I have a report that shows states where a product is available.  It's messy at best in the display.  What I would rather do, is show the states where the product is not available.  Easy enough in Excel but not sure about Power BI.

Do I add a column with all 50 states and write a formula that says to display only states from the data that don't appear in the list of 50?

Any help is appreciated.

[gmsamborn]

Hi @datadelta 

I'm not sure how you want to use it so I wrote a couple of measures.  (In your title for the thread it seems if you are unsure whether or not to use a calculated column.)

In my sample data, i deleted all sales for States that start with "M".

Not in List = 
VAR _State = SELECTEDVALUE( 'DimState'[Abbrev] )
VAR _List = DISTINCT( 'FactSales'[Abbrev] )
VAR _Result =
    IF(
        HASONEVALUE( 'DimState'[Abbrev] )
            && NOT ( _State IN _List ),
        "No Sales"
    )
RETURN
    _Result

OR

Not in List - CONCATENATEX = 
VAR _AllStates = DISTINCT( 'DimState'[Abbrev] )
VAR _StatesWithSales = DISTINCT( 'FactSales'[Abbrev] )
VAR _StatesWithNOSales = EXCEPT( _AllStates, _StatesWithSales )
VAR _Result =
    CONCATENATEX(
        _StatesWithNOSales,
        [Abbrev],
        ", ",
        [Abbrev]
    )
RETURN
    _Result

Let me know if you have any questions.

NOT in a list.pbix

[datadelta]

THank you.  So, in my case, I have to have the formula identify that the state is not in the list.

Example from data set:

AL,AK,AZ,AR,CO,CT,DE,DC,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MN,MS,MO,MT,NE,NV,NH,NJ,NM,NC,ND,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY,GU,PR

I need the formula to bring back any states not listed here.  Will your solution do that?

[gmsamborn]

Please show how you are using it. (ie.  are you using it in a table, card, filter, etc? )

[techies]

Hi @datadelta 

Yes, you can add a table with all 50 states and then add a calculated column like this:

IsProductUnavailable =
IF(
NOT(AllStates[State] IN VALUES(ProductAvailability[State])),
"Not Available",
"Available"
)
Next, in the table visual, you can filter to show only the 'Not Available' states.

[datadelta]

Okay.  Here's what I'm needing to solve and I truly appreciate your help.

I need the states that are not listed in the dataset to return the state name.  So, if the set has NY, CT, MA, DE, etc I need the states not in the listing to come back to be used in the states not available and show them.  I hope that makes sense

[techies]

Hi @datadelta yes i understand. You want to identify and display the states not present in your dataset.

please create calculated table like this - 

NotAvailableStates =

EXCEPT(

    ALL(AllStates[State]),

    VALUES(ProductAvailability[State])

)

[gmsamborn]

I would like to help but I'm a still little unclear about your requirements.

Can you provide the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

A .pbix file with sample data would be best.

[gmsamborn]

If you want a list of states not in the list, you can add the [State] from your state dimension table to a table visual and filter the visual based on [NotInList] as not blank.

I hope I understood.

[parry2k]

@datadelta check this video on my channel, address something similar, you can tweak the solution as per your need: Smart Title based on selection EXCEPT DAX Function - Power BI