Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I have a table 'MP' that stores the following:
[ID]: a case ID
[MNr]: a material ID
[Amount]: the amount of material used
Within each case, multiple different materials can be used with varying amounts.
The table can look like this:
ID | [MNr] | [Amount]
1 | 11 | 4
1 | 10 | 2
1 | 12 | 25
1 | 20 | 20
2 | 20 | 20
2 | 5 | 6
2 | 10 | 30
2 | 8 | 1
3 | 10 | 7
3 | 3 | 8
3 | 22 | 10
4 | 14 | 1
4 | 20 | 3
I am currently showing the sum of [Amount] per [MNr], which is pretty straightforward.
So, as an example, [MNr] 20 would have the [Amount] 43 (20 from case 1, 20 from case 2, 3 from case 4).
Now, assume i select a certain material [MNr].
I want to obtain the material [MNr] that is changed most often together with the selected material within the same cases.
To continue the example from above, when selecting/showing [MNr] 20, the calculation should return [MNr] 10 with an [Amount] of 32 ([MNr] 20 and 10 occur togehter in cases 1 and 2. The sum of [Amount] from [Mnr] 10 is 32).
Even though [MNr] 10 occurs in case 3 as well, it's not together with [MNr] 20 such that it should not count.
Currently, after many other failures, I am trying the following:
Solved! Go to Solution.
Hello @_96bp,
Can you please try this DAX approach:
TopRelatedMaterial =
VAR SelectedMNr = SELECTEDVALUE(MP[MNr]) -- Assuming there's a way to select a single MNr, like a slicer.
VAR RelatedMaterials =
CALCULATETABLE(
SUMMARIZE(MP, MP[ID], MP[MNr], "TotalAmount", SUM(MP[Amount])),
NOT(ISBLANK(MP[Amount])),
MP[MNr] <> SelectedMNr
)
VAR SharedCasesWithSelectedMNr =
CALCULATETABLE(
DISTINCT(MP[ID]),
MP[MNr] = SelectedMNr
)
VAR FilteredRelatedMaterials =
FILTER(
RelatedMaterials,
MP[ID] IN SharedCasesWithSelectedMNr
)
VAR SummarizedRelatedMaterials =
SUMMARIZE(
FilteredRelatedMaterials,
MP[MNr],
"SumAmount", SUMX(FILTEREDRelatedMaterials, [TotalAmount])
)
VAR RankedMaterials =
ADDCOLUMNS(
SummarizedRelatedMaterials,
"Rank", RANKX(SummarizedRelatedMaterials, [SumAmount],, DESC, Dense)
)
RETURN
MAXX(FILTER(RankedMaterials, [Rank] = 1), MP[MNr])
Hello @_96bp,
Can you please try this DAX approach:
TopRelatedMaterial =
VAR SelectedMNr = SELECTEDVALUE(MP[MNr]) -- Assuming there's a way to select a single MNr, like a slicer.
VAR RelatedMaterials =
CALCULATETABLE(
SUMMARIZE(MP, MP[ID], MP[MNr], "TotalAmount", SUM(MP[Amount])),
NOT(ISBLANK(MP[Amount])),
MP[MNr] <> SelectedMNr
)
VAR SharedCasesWithSelectedMNr =
CALCULATETABLE(
DISTINCT(MP[ID]),
MP[MNr] = SelectedMNr
)
VAR FilteredRelatedMaterials =
FILTER(
RelatedMaterials,
MP[ID] IN SharedCasesWithSelectedMNr
)
VAR SummarizedRelatedMaterials =
SUMMARIZE(
FilteredRelatedMaterials,
MP[MNr],
"SumAmount", SUMX(FILTEREDRelatedMaterials, [TotalAmount])
)
VAR RankedMaterials =
ADDCOLUMNS(
SummarizedRelatedMaterials,
"Rank", RANKX(SummarizedRelatedMaterials, [SumAmount],, DESC, Dense)
)
RETURN
MAXX(FILTER(RankedMaterials, [Rank] = 1), MP[MNr])
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |