CODEWITHSUNDEEP
powerbidax
Arlaf
Arlaf

Reputation: 339

DAX lookupvalue from calculated table

I need to add a column in a table containing the minimum value of a group of another table.

I have a Table A with unique IDs.

enter image description here

In this table I would need to add a column min_value containing the minimum value for each ID in another table Table B :

enter image description here

I know I could use SUMMARIZE() on my Table B in order to create a calculated table with the minimum value of each ID, save this Table C and then use LOOKUPVALUE() between my Table A and Table C to get the column I need in Table A. But I don't want to save this Table C because it would not be used again, and I did not find a way to use the result of my SUMMARIZE() directly in my LOOKUPVALUE() without saving the calculated table.

Upvotes: 0

Views: 5029

Answers (1)

smpa01
smpa01

Reputation: 4346

Please try to provide sample data in table format here.

There are quite a few ways to achieve what you want depending on what is the relationship between these two tables and whether you want a calculated column or measure.

Calculated Column - with relationship between 'Table A'[ID] and 'Table B'[ID]

ALLEXCEPT = --can be utilized as a measure too
CALCULATE ( MIN ( 'Table B'[Value] ), ALLEXCEPT ( 'Table A', 'Table A'[ID] ) )

RELATEDTABLE =
    MINX ( RELATEDTABLE ( 'Table B' ), 'Table B'[Value] )
    
IN =
CALCULATE (
    MINX ( 'Table B', 'Table B'[Value] ),
    'Table A'[ID] IN VALUES ( 'Table B'[ID] )
)

Calculated Column - with no relationship between 'Table A' and 'Table B'

TREATAS =
CALCULATE (
    CALCULATE (
        MINX ( 'Table B', 'Table B'[Value] ),
        TREATAS ( VALUES ( 'Table A'[ID] ), 'Table B'[ID] )
    )
)

Measure - with relationship between 'Table A'[ID] and 'Table B'[ID]

_in =
CALCULATE (
    MIN ( 'Table B'[Value] ),
    'Table A'[ID] IN VALUES ( 'Table B'[ID] )
)

Measure - with no relationship between 'Table A' and 'Table B'

_treatas =
CALCULATE (
    MIN ( 'Table B'[Value] ),
    TREATAS ( VALUES ( 'Table A'[ID] ), 'Table B'[id] )
)

Upvotes: 1

Related Questions