Forum Discussion
Saxon202202
Helper III
3 years agoMin and max based in-between two tables
Hi, I have two tables are Data and Report. In data table contain the following columns are Item, country code, sale qty. In report table contain the following columns are item and project co...
smpa01
Community Champion
3 years agoSaxon10 please clearly provide sample data (not pic) and expected output
Saxon10
Post Prodigy
3 years agosmpa01, I drop the power bi file here incuding desired results.
https://www.dropbox.com/s/d2siyuqqwaklhv4/14-02-2023.pbix?dl=0
- smpa013 years ago
Community Champion
Saxon10 PFA
__countryCodeFromData = VAR __data = //FILTER Data by Report based on Data[Item] = Report[Item] //SELECT only Data[Couuntry Code] from the above join of two tables //and rather simply selecting the field create a copy of the same field to break the data lineage if any // The result will be a single row table like {"ADMK", "DMK"} SELECTCOLUMNS ( SUMMARIZE ( FILTER ( Data, ( Data[Item] ) IN ( SUMMARIZE ( Report, Report[Item] ) ) ), Data[Country Code] ), "cc", [Country Code] & "" ) // count rows of the above table VAR __cnt = COUNTROWS ( __data ) // create a black list; these are the items need to be removed from the __data tbl VAR __intermediate = DATATABLE ( "cc", STRING, { { "ADMK" } } ) //left anti join self-explanatory VAR __anti = EXCEPT ( __data, __intermediate ) // if __cnt >1 {"ADMK", "DMK"} then count the anti-join rows else count the filter table rows RETURN IF ( __cnt > 1, MAXX ( __anti, [cc] ), MAXX ( __data, [cc] ) ) __salesQTFromData = VAR __item = MAX ( Report[Item] ) VAR __cc = [__countryCodeFromData] RETURN CALCULATE ( MAX ( Data[Sale Qty] ), FILTER ( Data, Data[Item] = __item && Data[Country Code] = __cc ) )- smpa013 years ago
Community Champion
You can also replace the last measure with this (this one might be faster)
Measure = VAR __item = MAX ( Report[Item] ) VAR __cc = [__countryCodeFromData] RETURN CALCULATE ( MAX ( Data[Sale Qty] ), TREATAS ( CROSSJOIN ( SELECTCOLUMNS ( { __item }, "item", [Value] ), SELECTCOLUMNS ( { __cc }, "cc", [Value] ) ), Data[Item], Data[Country Code] ) )