DAX Measure: check each row in a data subset for a specific value

Question

I try to check for each row if a DAX measure is <0. However, the check shall be done for each ID separately and not for the whole column. If TRUE, all rows related to this specific ID shall use measure2. If all rows within an ID are positive measure1 shall be used

Example

I tried to combine different DAX functions, like filter, if, etc. but didn´t get the job done.

Marcus · Accepted Answer · 2024-06-05 18:08:29Z

1

I would try to implement a solution based on COUNTROWS, ALLEXCEPT and FILTER.

First count number of rows for a given ID using ALLEXCEPT.

Then do the same, but also use FILTER on the table returned by ALLEXCEPT where you use the required condition.

Compare the values and return the appropriate measure based on the logical predicate.

New measure =
VAR _id_count =
    COUNTROWS ( ALLEXCEPT ( 'Table', 'Table'[ID] ) )
VAR _id_count_where_value1_positive =
    COUNTROWS (
        FILTER ( 
            ALLEXCEPT ( 'Table', 'Table'[ID] ), 
            [Value1] > 0 
        )
    )
RETURN
    IF ( 
        _id_count = _id_count_where_value1_positive, 
        [Value1], 
        [Value2] 
    )

answered Jun 5, 2024 at 18:08

Marcus

4,0251 gold badge7 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

DAX Measure: check each row in a data subset for a specific value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related