cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver II
Resolver II

Calulated column question

Power BI community

 

I have a table of sales data that amongst other things contians:

 

Part Number        Cust Type          Part Type

Part1                    A                       B

Part1                    B                       B

Part2                    B                       A

Part3                    B                       B

 

I am trying to calculate a column to enable me to sliice data to show part numbers that were B type and only purchased by B type customers so in the eample above it only Part 3 would be true as Part 2 was purchased by an A customer and Part 1 was purchased by both and A and a B customer.

 

Can anyone help?

 

Thanks

 

Dan

1 ACCEPTED SOLUTION

kudos to CS for putting in so much time.  I am following this post because I come from the SQL world and it is a classic Unmatch query; and so I'm interested to see how it is implemented in PBI.

 

In SQL one would have a Parts table (all Parts & Part Type only, no repeats)

 

Then you would make the 1st unmatched record set which is those sales records where the 2 types do not match. 

That would then be made distinct of Part field only so there are no repeats: DistinctUnMatch1

 

Then you would left outer join Parts to DistinctUnMatch1.

That returns all Parts records but in the UnMatch1 Parts field there are nulls where nothing can match.

This record set is UnMatch2. ... you apply criteria so it only returns the Nulls records- which by definition is the record set that are the Parts with only matched Customer & Part Type.

 

I may be out of date on PBI capability but I don't think one can define a left outer join - so I presume that is why one is working with yes/no comparisons instead and then a filter.

 

 

 

www.CahabaData.com

View solution in original post

13 REPLIES 13
Super User II
Super User II

Hi @robofski,

 

@CheenuSing has done some great work, but I often suggest completing this type of complicated output using the Query Editor.


The query editor is very robust and can create an additional column a lot easier and in a step by step process, which makes it easier to work through.

 

As well as if you create it using the Query Editor, it will get better compression into your data model.





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

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ

 

I agree it could be better with Query Editor and can walk through step by step.

 

Next time will look at this option, instead of measures.

 

Thanks for the valuable feedback.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Community Champion
Community Champion

Hi @robofski

 

Try the following

 

1. create a column called countrows as

    Countrows = Calculate(Countrows(PartsBought),FIlter(PartsBought,PartsBought[PartNo]=Earlier([PartNo])))

    This finds the number of rows by part number

2. Create a column called ShowYes as

    ShowYes = If ([Countrows]=1 && [Cust Type] = "B" && [Part Type] = "B", "Yes","No")

    If the number of rows is 1 and custtype and parttype are "B" then set that row to "Yes" to show other wise "No"

3. Create the table report and in the Filters include the value ShowYes and set the Advance Filtering as SHow item if it contains  "Yes".

 

If this works for you please accept it as  a solution and also gice KUDOS.

 

Cheers

 

CheenuSing 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

I may have been a little premature in thinking this was the answer, it doesn't appear to be working quite as expected, looks to be only returning results that only appear once with Yes, however records that appear multiple times, even if they are B parts sold to B customers are appearing as No, so if I B customer buys the same B part multiple times and is the only customer type that buys that part it should be a Yes.

Hi

 

Please share some data and the output expected.  Put it on onedrive or dropbox and share the link

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSinghere's a link to a data sample https://watlow365-my.sharepoint.com/personal/drobson_watlow_com/_layouts/15/guestaccess.aspx?docid=0...

 

If the 2016Part_AB is a B and all the 2016Cust_AB for that part are also B then I want to say it's a B Cust exclusive part as no A's purchased it.  If A and B customers then it's not B exclusive and again if the only cust that purchased are A then it's not exclusive.

 

Does that make sense?

 

Dan

Hi Dan,

 

Here is the link to the pbix file developed based on the excel data in the link provided by you.

 

https://1drv.ms/u/s!ApP3mBZyGaHfgSx1iDQZppVnEAg7

 

Note the measures - BBRows, CountRows, ShowYes and the column BothBB added to the table.

 

Check it out and let me know if it still does not resolve your issue.

 

Cheers

CheenuSing

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Wow @CheenuSing you're really giving this your all, I appreciate the effort.

 

I think you're giving me the right thing but I need to be able to slice by Yes/No and as it's a measure not a column that's not possible unless I'm missing something in your solution approach.

Hi 

 

You can also use ShowYes as a column.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

kudos to CS for putting in so much time.  I am following this post because I come from the SQL world and it is a classic Unmatch query; and so I'm interested to see how it is implemented in PBI.

 

In SQL one would have a Parts table (all Parts & Part Type only, no repeats)

 

Then you would make the 1st unmatched record set which is those sales records where the 2 types do not match. 

That would then be made distinct of Part field only so there are no repeats: DistinctUnMatch1

 

Then you would left outer join Parts to DistinctUnMatch1.

That returns all Parts records but in the UnMatch1 Parts field there are nulls where nothing can match.

This record set is UnMatch2. ... you apply criteria so it only returns the Nulls records- which by definition is the record set that are the Parts with only matched Customer & Part Type.

 

I may be out of date on PBI capability but I don't think one can define a left outer join - so I presume that is why one is working with yes/no comparisons instead and then a filter.

 

 

 

www.CahabaData.com

View solution in original post

Hi there

You can indeed do a left join or any kind of join in the query editoe. It is called Merge and gives you all the options once you select it.




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

Proud to be a Super User!







Power BI Blog

@CahabaDataIn the end I figured this would be easier to do in a staging table before Power BI and that's what I've ended up doing.

 

@CheenuSingThank you for the help, whilst it ultimatley didn't work out, I leart a lot from this thread which I'm sure will come in useful somewhere along my Power BI journey (I'm a relative newbie)

Awesome!  Thank you!

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors