Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 code.
In both tables the item column contains duplicate entry. Data and report tables are many to many relationships.
I want country code and sale qty according to the item into report table.
Note:
The same item has two different country code are ADMK & DMK. If same item has two different country code, then DMK is first concern and ADMK is second concern. I want unique code against each item within the table.
The same item has different sale qty or same qty according to the country code.
I am looking for measure to achieve the result.
https://www.dropbox.com/s/d2siyuqqwaklhv4/14-02-2023.pbix?dl=0
@Saxon202202 does this measure work when you drop it on DATA
measure= maxx(relatetable('fact'), 'fact'[countryCode]))
Many to many is not best practice but I will leave arguing for that portion and simply going to assume that you have a business case.
I refrain from working with many to many but I can see that it works with an indirect relationship without a USERELATIONSHIP callback
@smpa01, Thanks for your reply. It means I need to remove the relationship in between two tables?
Is that possible to create a unique count for country code, TOPN in order to filter the result in report table? I tried couple of function but it doesn't work for me.
@Saxon10 please clearly provide sample data (not pic) and expected output
@smpa01, I drop the power bi file here incuding desired results.
https://www.dropbox.com/s/d2siyuqqwaklhv4/14-02-2023.pbix?dl=0
@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 )
)
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]
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
106 | |
88 | |
74 | |
69 |
User | Count |
---|---|
123 | |
112 | |
95 | |
83 | |
73 |