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.
Greetings to Everyone and Happy New Year 2018!
I have a unique problem that I have not been able to solve in DAX without a circumvented and inefficient method. The problem I am having is that the dax measure needs to be a dynamic, time intelligent accrual of cumulative totals within date segments.
Here is an example where the first image is MASTERDATASET:
Date Range = Dynamic - but for this example, the time slicer is set for 1/1/17 to 1/11/17
I want the subtotals of Value1 within that date range.
The wrinkle is in how the subtotals are aggregated.
The result comes from:
Identifier1 AA has a combination with Identifier2 of
AA BB, AA DD, AA CC. So I want to total all of Value1 in the instances where BB, CC, DD are Identifier1 in the date range.
Measure = Subtotal for AA = sum Value1 where BB, CC, DD is Identifer1 = 8 + 5 + 7 + 8 = 28
But I want this for all distinct values in Identifier1. End result in the formula would subtotal all for AA, BB, CC, DD
AA = (BB AA) 8 + (CC BB) 5 + (CC AA) 8 + (DD AA) 7 = 28
description: because AA has 3 occurences in Identifier1 column with BB in Identifier2 [AA BB on 1/1/17], CC in Identifier2 [AA CC on 1/11/17], and DD in Identifier2 [AA DD on 1/5/17]. therefore, I want the total of Value1 in all occurences of BB, CC, DD as it occurs in Identifier1.
...furthermore, in PowerBI I would be able to also have it specify...
BB = (AA BB) 10 + (AA DD) 6 + (AA CC) 12 = 28
description: because BB has only 1 occurence in Identifier1 column with AA in Identifier2 [BB AA on 1/2/17]. Yet, AA has 3 occurences in Identifier1, as noted above. Therefore, I want all of the totals where AA is in Identifier1.
CC = (BB AA) 8 + (AA BB) 10 + (AA DD) 6 + (AA CC) 12 = 36
description: CC has 2 occurences in Identifier1 with BB in Identifier2 [CC BB on 1/5/17] and AA in Identifier2 [CC AA on 1/2/17].
...and so on...
DD = (AA BB) 10 + (AA DD) 6 + (AA CC) 12 = 28
description: again, because there is only 1 occurence of DD in Identifier1 with the combo of AA in Identifier2 [DD AA on 1/8/17].
The measure would then produce the following visualization table dynamically in Power BI:
I would then want the numbers to dynamically change if I changed the date range to 1/1/17 to 1/2/17.
AA BB 1/1/17
BB AA 1/2/17
CC AA 1/2/17
The measure would produce the following visualization dynamically in Power BI in table format:
I hope this is not as confusing as I feel it reads. This seems like it would be very simple to generate, but I cannot get it to work.
I would really appreciate the forum's assistance to help me crack the code here.
Solved! Go to Solution.
Hi @rymerco,
Based on my test, the formula below show work in your scenario.
Measure = VAR currentIdentifier1 = FIRSTNONBLANK ( Table1[Identifier1], 1 ) RETURN SUMX ( FILTER ( ALLSELECTED ( Table1 ), CONTAINS ( FILTER ( ALLSELECTED ( Table1 ), Table1[Identifier1] = currentIdentifier1 ), Table1[Identifier2], Table1[Identifier1] ) ), Table1[Value1] )
Here is the sample pbix file for your reference.
Regards
Hi @rymerco,
Based on my test, the formula below show work in your scenario.
Measure = VAR currentIdentifier1 = FIRSTNONBLANK ( Table1[Identifier1], 1 ) RETURN SUMX ( FILTER ( ALLSELECTED ( Table1 ), CONTAINS ( FILTER ( ALLSELECTED ( Table1 ), Table1[Identifier1] = currentIdentifier1 ), Table1[Identifier2], Table1[Identifier1] ) ), Table1[Value1] )
Here is the sample pbix file for your reference.
Regards
This appears to be the fix. Thank you for your assistance. Much appreciated as I was spinning in circles trying to find a more efficient solution.
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |