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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
FJV
Frequent Visitor

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?

1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

 

 

// 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.

View solution in original post

3 REPLIES 3
daXtreme
Solution Sage
Solution Sage

 

 

// 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
Frequent Visitor

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?

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.