Display values from list1 which are not present in list2

[Tony09]

Hello,

I am new to PowerBI, I have list1, list2. list1 has one column(Title) with all the values list2 is the responses submitted by the user. Now I want to display Title column values which are not present in list2 and present in list1 in donut chart or any charts.

List1:                      List2:                    List2:

Title column.       Title column      State column 

A.                          A.                       Abc

B.                          C.                       Abc

C.                                                    Xyz

D.                                                    Xy

E.                                                     Xyz

F.                          

I want to display the values which are not present in list2 (based on the state column selection), present in list1 as B,D,E,F in the PowerBI 

[Kedar_Pande]

Hi Tony,

You can use the EXCEPT DAX function. It will return the rows of left-side which do not appear on the right side.

Let me know if this doesn't resolve the requirement 

[Tony09]

@Kedar_Pande No it's not working for me. I tried with join also.

Because I want the values based on the state column selection from list2.

I have created a PowerBI report with

States column from list2 in slicer

Now based on the state selection I am showing the no of title(column from list2) for that particular sates selection.

Now I also wanted to show titles which are not present in list2 based on the state selection from list1.

List1 has the list of titles.

[mangaus1111]

Hi Tony,

if you want to use the DAX function EXCEPT, you have to transform List1 and List2 in tables.

Please see my pbi file and let me know if you can not open it.

Display values from list1 which are not present in list2.pbix

[Tony09]

Hi @mangaus1111 

I am unable to open this pbix file. 

Yes I have transformed both the lists in tables. 

In list1 I have only one column - Title(List of all titles)

In list2 I have 2 columns 1)Title, 2) States

So based on the state selection in the PowerBI report I want to check how many rows have responded from title column and how many titles have not responded yet for that particular state.

[v-rongtiep-msft]

Hi @Tony09 ,

Please refer to my pbix file to see if it helps you.

let
    List1 = {"A", "B", "C", "D", "E", "F"},
    List2 = {"A", "C"},
    ListDiff = List.Difference(List1, List2),
    TableDiff = Table.FromList(ListDiff, Splitter.SplitByNothing(), {"Title"})
in
    TableDiff

Or you can create a new table.

ListDiff = EXCEPT (List1, List2)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Tony09]

Thanks for the reply @v-rongtiep-msft 

I tried using the below formula but the table is empty

Table = Except(VALUES(List1['Title']), VALUES(List2['Title']))

In list1 I have only one column - Title(List of all titles)

In list2 I have 2 columns  1)Title, 2) States

So based on the state selection in the PowerBI report I want to check how many rows have responded from title column and how many titles have not responded yet for that particular state.

Could you please help me with this.