Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I have a weird dataset, and very limited control over it, i.e. I can't transform data in Power Query (don't ask).
The data basically looks like the table below and I need to be able to count the SUM of column 2 by any ID in column 1 or a combination of them.
The logic is not set in stone, so users need a degree of control over how to slice the data. For example, some may need to know the SUM of all instances of 1-UEVLP regardless of other IDs, but others will only need that ID only; rinse and repeat for each ID in the list.
The IDs can come in any order and there can be one or more in each record. I have a list of all possible IDs at this point but would like to make it dynamic in case new ones will pop up in the future.
I thought I was pretty good with DAX by now, but I don't even know where to start. I will need to build some sort of temporary tables summarising the values, I guess?
Any ideas?
1-UEVLP,a090001,a092002,a095001,a095003 | 325716 |
1-UEVLP,a090001,a092002,a095001,a101001 | 306857 |
a016001,a090002,a095002,a095003,a101001 | 1171551 |
1-UEVLP,a016001,a090001,a090002,a092002,a095001 | 1507506 |
1-UEVLP,a016001,a090001,a090002,a095001,a095002 | 1607063 |
1-UEVLP,a016001,a090001,a090002,a095001,a095003 | 1575770 |
1-UEVLP,a016001,a090001,a090002,a095001,a100002 | 1547156 |
1-UEVLP,a016001,a090001,a090002,a095001,a101001 | 1534487 |
1-UEVLP,a016001,a090001,a090002,a092002,a095001,a095002 | 1650842 |
Solved! Go to Solution.
This solution uses the pattern provided by Chris Webb. I wrapped the base pattern in GENERATE, in order to iterate each row of the table.
1. Create a calculated column to replace comma with pipe (vertical bar). The pipe symbol is the delimiter of the PATHLENGTH function.
ID Pipe =
SUBSTITUTE ( Table1[ID Raw], ",", "|" )
2. Create calculated table:
Table2 =
GENERATE (
SUMMARIZE ( Table1, Table1[ID Pipe], Table1[Amount] ),
VAR vID = Table1[ID Pipe]
VAR vIDCount =
PATHLENGTH ( vID )
VAR vNumberTable =
GENERATESERIES ( 1, vIDCount, 1 )
VAR vIDTable =
GENERATE (
vNumberTable,
VAR CurrentKey = [Value] RETURN ROW ( "@ID", PATHITEM ( vID, CurrentKey ) )
)
VAR vResult =
SELECTCOLUMNS ( vIDTable, "ID", [@ID] )
RETURN
vResult
)
Proud to be a Super User!
This solution uses the pattern provided by Chris Webb. I wrapped the base pattern in GENERATE, in order to iterate each row of the table.
1. Create a calculated column to replace comma with pipe (vertical bar). The pipe symbol is the delimiter of the PATHLENGTH function.
ID Pipe =
SUBSTITUTE ( Table1[ID Raw], ",", "|" )
2. Create calculated table:
Table2 =
GENERATE (
SUMMARIZE ( Table1, Table1[ID Pipe], Table1[Amount] ),
VAR vID = Table1[ID Pipe]
VAR vIDCount =
PATHLENGTH ( vID )
VAR vNumberTable =
GENERATESERIES ( 1, vIDCount, 1 )
VAR vIDTable =
GENERATE (
vNumberTable,
VAR CurrentKey = [Value] RETURN ROW ( "@ID", PATHITEM ( vID, CurrentKey ) )
)
VAR vResult =
SELECTCOLUMNS ( vIDTable, "ID", [@ID] )
RETURN
vResult
)
Proud to be a Super User!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |