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.

[v-veshwara-msft]

Hi @datadelta ,

Thanks for reaching out to the Microsoft Fabric Community.

Thanks to @parry2k , @gmsamborn , and @techies for your responses.

Just checking in to see if you had a chance to review and try the solution suggested by @techies using the EXCEPT() calculated table to identify the states not present in your dataset.

If that approach didn’t fully meet your requirements or if you encountered any difficulties, another common method is to create a relationship between your AllStates table (containing the full list of states) and your product availability data table. Then, in your report visual, you can add the states from the AllStates table and apply a filter to show only those where the related product availability state is blank. This approach helps you identify missing states without needing additional DAX calculations, which can simplify your report.

Hope this helps. Please reach out for further assistance.

If you found another solution that worked for you, please share it here to help others in the community.

Also, if any of the responses in this thread were helpful, kindly mark the appropriate answer as the accepted solution to assist others with similar questions. A kudos would also be appreciated.

Thanks.

[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