Forum Discussion

FJV's avatar
FJV
Frequent Visitor
3 years ago
Solved

Explicit vs implicit measure with value on the one side

Consider the following situation with to tables in a optional one (A)-to-many (B) relation and an value on the one side:

 

Table A

TableA_ID, Value

A, 10

B, 20

C, 30

 

Table B

TableB_ID, Description, TableA_ID

V, DescrV, A

W, DescrW, A

X, DescrX, B

Y, DescrY, null

Z, DescrZ, null

 

I want to visualize the table:

TableA_ID, Value_measure, TableB_D, Description

A, 10, V, DescrV

A, 10, W, DescrW

B, 20, X, DescrX

Total 30

 

Using an implicit Power BI measure for Value_measure, this works out of the box.

But I want to create an explicit measure (fomula). Using Value_measure = SUM(A[Value]) gives more rows than desired. 

How can this be explained and solved?

  • daXtreme's avatar
    daXtreme
    3 years ago

     

     

    // Given:
// A ( 1 -- one-way-filtering --> : ) B 

[Your Measure] =
SUMX(
    SUMMARIZE(
        'Table B',
        'Table A'[TableA_ID],
        'Table A'[Value]
    ),
    'Table A'[Value]
)

     

     

     In Power BI it DOES MATTER which column you pull from which table... so you have to be careful how you code your measures.

3 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    3 years ago

     

     

    // Given:
// A ( 1 -- one-way-filtering --> : ) B 

[Your Measure] =
SUMX(
    SUMMARIZE(
        'Table B',
        'Table A'[TableA_ID],
        'Table A'[Value]
    ),
    'Table A'[Value]
)

     

     

     In Power BI it DOES MATTER which column you pull from which table... so you have to be careful how you code your measures.

    • FJV's avatar
      FJV
      Frequent Visitor
      3 years ago

      Thanks a lot! This works fine and I can understand the solution.

      But I am still wondering why the implicit meassure acts different than a sum(A[value]). When reading about implicit measures (those given by Power BI to numeric fields with the sigma-sign), the suggestion is given that it does a simple SUM. Apparently not...? Does anyone have some details on this?

      • daXtreme's avatar
        daXtreme
        Icon for Solution Sage rankSolution Sage
        3 years ago

        Rule of thumb: Never use implicit measures. NEVER. Always write your own. You'll save yourself time, effort and grief.