Forum Discussion
Calulated column question
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.
Hi
Please share some data and the output expected. Put it on onedrive or dropbox and share the link
Cheers
CheenuSing
Anonymoushere's a link to a data sample https://watlow365-my.sharepoint.com/personal/drobson_watlow_com/_layouts/15/guestaccess.aspx?docid=0dd704ae420a142f59c52e39b4f40f3ad&authkey=AU2rUQn5F6UzpTUWmMfJtNg&expiration=2017-02-25T10:12:31.000Z
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
Wow Anonymous 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.
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.