Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear all,
not sure if my title makes sense, but I'll try to explain.
Description
I've got two tables:
Table A has the information I would like to display in Table B. Name in Table A is always blank on Level 1.0. All Names of Table B are in Table A, but Table A might have more names. Also, some Names of Table B could be Manager. There is always only one Manager per ID, but there might be different names per ID.
Active relationship: Table A[Manager] (many side) : Table B [Name ] (one side)
Deactivated relationship: Table A [Name] (many side) : Table B[Name] (one side)
By default, I would like to keep the currently active relationship, because I do other calculations with it. So, If required, the relationship should be changed within the calculation.
Table A
Level | ID | Value | Manager | Name |
1.0 | 123 | 100 | Z | |
1.1 | 123 | 50 | Z | A |
1.1.1 | 123 | 30 | Z | A |
1.1.2 | 123 | 20 | Z | B |
1.2 | 123 | 50 | Z | E |
1.0 | 456 | 300 | C | |
1.1 | 456 | 150 | C | C |
1.2 | 456 | 100 | C | D |
1.3 | 456 | 50 | C | E |
1.0 | 789 | 400 | X | |
1.1 | 789 | 300 | X | A |
1.2 | 789 | 100 | X | C |
Table B
Name |
A |
B |
C |
D |
E |
Task
I want to use the Name of Table B and get the sum of Value for Level 1.0, only once per ID for the current Name.
e.g.: Name A is present twice for ID123, but Value should be used only once. However, Value should be used again for Name B and E.
Table B (example for ID123)
Name | Value |
A | 100 |
B | 100 |
C | 0 |
D | 0 |
E | 100 |
To make it even more complex: if the current Name is equal to Manager, Value should not be used for this name. But, still for others
Table B (example for ID456)
Name | Value |
A | 0 |
B | 0 |
C | 0 |
D | 300 |
E | 300 |
Table B (final result)
Explanation is not part of the table.
Name | Sum of Value Level 1.0 | Explanation |
A | 500 | Value (ID123, Level 1.0) + Value (ID789, Level 1.0) |
B | 100 | Value (ID123, Level 1.0) |
C | 400 | Value(ID789, Level 1.0) |
D | 300 | Value (ID456, Level 1.0) |
E | 400 | Value (ID123, Level 1.0) + Value (ID456, Level 1.0) |
I have been trying to figure this out in DAX the whole week. I hope my explanation is clear and you understand what I am trying to do. I am looking forward to your suggestions on this.
many thanks!
Dan
Solved! Go to Solution.
I have managed to create a calculated column in Table B which calculates value correctly. The only exception is, that I have not yet taken Manager into account. I would still appreciate some feedback whether this is the best solution in terms of readability and performance.
Value =
SUMX ( CALCULATETABLE ( FILTER ( FILTER ( ALL ( 'Table A' ); ( 'Table A'[ID] IN VALUES ( 'Table A'[ID] ) ) ); 'Table A'[Level] = "1.0" ); FILTER ( 'Table A'; SEARCH ( 'Table B'[Name]; 'Table A'[Name];; 0 ) > 0 ) ); 'Table A'[Value] )
many thanks.
Task
I want to use the Name of Table B and get the sum of Value for Level 1.0, only once per ID for the current Name.
e.g.: Name A is present twice for ID123, but Value should be used only once. However, Value should be used again for Name B and E.
Table B (example for ID123)
Name Value A 100 B 100 C 0 D 0 E 100
To make it even more complex: if the current Name is equal to Manager, Value should not be used for this name. But, still for others
Table B (example for ID456)
Name Value A 0 B 0 C 0 D 300 E 300
Table B (final result)
Explanation is not part of the table.
Name Sum of Value Level 1.0 Explanation A 500 Value (ID123, Level 1.0) + Value (ID789, Level 1.0) B 100 Value (ID123, Level 1.0) C 400 Value(ID789, Level 1.0) D 300 Value (ID456, Level 1.0) E 400 Value (ID123, Level 1.0) + Value (ID456, Level 1.0)
Could you clarify more details about the logic to generate Table B(example for ID123) and Table B(example for ID456)?
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
please find the explanations below.
Explanation for ID123
Name | Value | Explanation (ID123) |
A | 100 | Name A is present twice for ID123, but value (ID123, Level 1.0) should be used only once |
B | 100 | Name B is present once for ID123, therefore value (ID123, Level 1.0) should be used |
C | 0 | Name C is not present for ID123, therefore 0 |
D | 0 | Name D is not present for ID123, therefore 0 |
E | 100 | Name E is present once for ID123, therefore value (ID123, Level 1.0) should be used |
Explanation for ID456
Name | Value | Explanation (ID456) |
A | 0 | Name A is not present for ID456, therefore 0 |
B | 0 | Name B is not present for ID456, therefore 0 |
C | 0 | Name C is present for ID456, but Name C is also Manager for ID456, therefore 0 |
D | 300 | Name D is present once for ID456, therefore value (ID456, Level 1.0) should be used |
E | 300 | Name E is present once for ID456, therefore value (ID456, Level 1.0) should be used |
If possible, I would prefer a calculated column to use it for additions with other columns.
What I have been trying to do is to get a list of IDs where the current Name is present and where current Name is not equal to Manager. Then I would filter Table A by these IDs and summarize all values only for Level 1.0 and only once per ID. This should be iterated for all names in Table B. The Problem is if Table A is filtered by the current name from Table B, all rows with Level 1.0 will be filtered as well because Name in Table A is always empty for Level 1.0. Therefore, I would need to Filter original Table A by the results (IDs) of Table A filtered by Name. I think this requires nested filters, but I am getting confused with context.
Example Name A
Step 1: Filter Table A by Name from Table B
Level | ID | Value | Manager | Name |
1.1 | 123 | 50 | Z | A |
1.1.1 | 123 | 30 | Z | A |
1.1 | 789 | 300 | X | A |
Step 2: Use IDs for Name A (123 + 789) to filter original Table A
Level | ID | Value | Manager | Name |
1.0 | 123 | 100 | Z | |
1.1 | 123 | 50 | Z | A |
1.1.1 | 123 | 30 | Z | A |
1.1.2 | 123 | 20 | Z | B |
1.2 | 123 | 50 | Z | E |
1.0 | 789 | 400 | X | |
1.1 | 789 | 300 | X | A |
1.2 | 789 | 100 | X | C |
Step 3. Filter by Level = 1.0 (Table A)
Level | ID | Value | Manager | Name |
1.0 | 123 | 100 | Z | |
1.0 | 789 | 400 | X |
Step 4. Summarize Values (result for Table B)
Name | Value |
A | 500 |
Step 5: Repeat 1-4 for all Names in Table B
hope this helps.
I have managed to create a calculated column in Table B which calculates value correctly. The only exception is, that I have not yet taken Manager into account. I would still appreciate some feedback whether this is the best solution in terms of readability and performance.
Value =
SUMX ( CALCULATETABLE ( FILTER ( FILTER ( ALL ( 'Table A' ); ( 'Table A'[ID] IN VALUES ( 'Table A'[ID] ) ) ); 'Table A'[Level] = "1.0" ); FILTER ( 'Table A'; SEARCH ( 'Table B'[Name]; 'Table A'[Name];; 0 ) > 0 ) ); 'Table A'[Value] )
many thanks.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |