cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

is it possible to subtract two text value set in power bi ?

I have a column1 in which i have 15 values, those are employees name. In another column2 I have name of all the employees. I want to get those values those are present in column2 but not in column1.

column1    column2

a                  p

b                 q

c                  c

                   b

                   a

 

so the coutput should be p and q. 

basically i want column1 - column2.

Is there any function for that?

Thanks for your time.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: is it possible to subtract two text value set in power bi ?

@AnandRanga

 

i have a different way to solve this question:

 

1 create a measure

 

notincolumnb =
VAR col2 =
    VALUES ( Table1[Column2] )
RETURN
    IF (
        HASONEVALUE ( Table1[Column2] ),
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER ( ALL ( Table1 ), Table1[Column1] = col2 )
        )
            + 0
    )

2. Use a table visual with a visual level filter

 

notincolumnb.png




Lima - Peru

View solution in original post

7 REPLIES 7
Highlighted
Super User I
Super User I

Re: is it possible to subtract two text value set in power bi ?

In the query editor you can use Table.Join() and do a right anti-join. Here's my query code where I manually entered your two example columns as the first step:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUSpQitWJVkoCsgrBrGQgKxnMAjKSYIxEpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Anti" = Table.Join(
	Table.SelectColumns(#"Changed Type", {"Column1"}),
	"Column1",
	Table.SelectColumns(#"Changed Type", "Column2"),
	"Column2",
	JoinKind.RightAnti)
in
    #"Anti"

The last step named #"Anti" is the only important part here. The first argument in Table.Join is a table to use as an input. I want all of Column1 from the original table and nothing else, so I used Table.SelectColumns to give me a one-column table. The second argument is the first key for the join, that is the column that I want to compare to my second table. The third argument is a table containing only column2, and fourth argument is the column from that second table that I want to compare to column1 from my first table. Finally I tell it what kind of join to do. A right anti join will give me all of the rows from the second table that are not present in the first.

 

In DAX...I haven't figured it out yet. It will no doubt involve SUMMARIZE.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Champion
Community Champion

Re: is it possible to subtract two text value set in power bi ?

In the Query Editor you can also use the following code, where Data is your data table

= List.RemoveMatchingItems(Data[column2],Data[column1])
Specializing in Power Query Formula Language (M)
Highlighted
Community Champion
Community Champion

Re: is it possible to subtract two text value set in power bi ?

@AnandRanga

If you are unfamiliar with the joins - Look here

http://www.excelguru.ca/blog/2015/12/16/merge-tables-using-outer-joins-in-power-query/

and also here

http://www.excelguru.ca/blog/2015/12/23/merge-tables-using-inner-and-anti-joins-in-power-query/

 

Good Luck! Smiley Happy

 

EDIT: But again here's @MarcelBeug showing off with his 1 line solutions! Smiley Very Happy

Highlighted
Helper III
Helper III

Re: is it possible to subtract two text value set in power bi ?

@Sean

@MarcelBeug

@KHorseman

I am trying with IN operator like 'Product'[Color] IN { "Red", "Blue", "Black"} but I need NOT IN, can someone help me with this. If there is NOT IN available, I am done with it.

https://msdn.microsoft.com/en-us/library/ee634237.aspx

Thanks a lot.

 

Highlighted
Super User I
Super User I

Re: is it possible to subtract two text value set in power bi ?

@AnandRangaI'm confused. In your example you had two columns in a table that you wanted to compare. Now you say you have a manually written list of values? Which is it? Those are two very different scenarios.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: is it possible to subtract two text value set in power bi ?

@KHorseman

My expression is:

defaulter = IF(TIMESHEETANALYSISCUBE[WORKER NAME] IN {TIMESHEETANALYSISCUBE[WorkerNameDEF]},TIMESHEETANALYSISCUBE[WORKER NAME])

But I want to use NOT IN.

Thanks for your time.

Highlighted
Community Champion
Community Champion

Re: is it possible to subtract two text value set in power bi ?

@AnandRanga

 

i have a different way to solve this question:

 

1 create a measure

 

notincolumnb =
VAR col2 =
    VALUES ( Table1[Column2] )
RETURN
    IF (
        HASONEVALUE ( Table1[Column2] ),
        CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER ( ALL ( Table1 ), Table1[Column1] = col2 )
        )
            + 0
    )

2. Use a table visual with a visual level filter

 

notincolumnb.png




Lima - Peru

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors