Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dandutch
Frequent Visitor

Calculating the sum of values, only once per ID and filtered by another table

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

LevelIDValueManagerName
1.0123100Z 
1.112350ZA
1.1.112330ZA
1.1.212320ZB
1.212350ZE
1.0456300C 
1.1456150CC
1.2456100CD
1.345650CE
1.0789400X 
1.1789300XA
1.2789100XC

 

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)

 

NameValue
A100
B100
C0
D0
E100

 

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)

 

NameValue
A0
B0
C0
D300
E300

 

Table B (final result)

Explanation is not part of the table.

 

NameSum of Value Level 1.0Explanation
A500Value (ID123, Level 1.0) + Value (ID789, Level 1.0)
B100Value (ID123, Level 1.0) 
C400Value(ID789, Level 1.0)
D300Value (ID456, Level 1.0)
E400Value (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

1 ACCEPTED SOLUTION

@v-yuta-msft

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.

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@dandutch,

 


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.

@v-yuta-msft

 

please find the explanations below.

 

Explanation for ID123

NameValue Explanation (ID123)
A100Name A is present twice for ID123, but value (ID123, Level 1.0) should be used only once
B100Name B is present once for ID123, therefore value (ID123, Level 1.0) should be used
C0Name C is not present for ID123, therefore 0
D0Name D is not present for ID123, therefore 0
E100Name E is present once for ID123, therefore value (ID123, Level 1.0) should be used

 

Explanation for ID456

NameValue Explanation (ID456)
A0Name A is not present for ID456, therefore 0
B0Name B is not present for ID456, therefore 0
C0Name C is present for ID456, but Name C is also Manager for ID456, therefore 0
D300Name D is present once for ID456, therefore value (ID456, Level 1.0) should be used
E300Name 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 

LevelIDValueManagerName
1.112350ZA
1.1.112330ZA
1.1789300XA

 

Step 2: Use IDs for Name A (123 + 789) to filter original Table A

LevelIDValueManagerName
1.0123100Z 
1.112350ZA
1.1.112330ZA
1.1.212320ZB
1.212350ZE
1.0789400X 
1.1789300XA
1.2789100XC

 

Step 3. Filter by Level = 1.0 (Table A)

LevelIDValueManagerName
1.0123100Z 
1.0789400X 

 

Step 4. Summarize Values (result for Table B)

NameValue 
A500

 

Step 5: Repeat 1-4 for all Names in Table B

 

hope this helps.

@v-yuta-msft

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.