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 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
Awesome! Thank you!