Forum Discussion

liselotte's avatar
liselotte
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Measure excluding row context

I have tables: - dimension table dim_Model with columns Model UID, Model Name, Factory (each Model UID has 1 unique Model Name and vice versa) - dimension table dim_Style with columns Style UID, St...
DAX
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi liselotte ,

     

    Please try this formula:

    nr_Metallic_Accessories_2 = 
IF(MAX('fact_Accessory'[Is_Metallic]) in {0,1},
CALCULATE(
	SUM('fact_Accessory'[Is_Metallic]),
	ALLEXCEPT('fact_Accessory','fact_Accessory'[Model UID])
))

     

    Result:

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks for the replies from Poojara_D12 and PhilipTreacy.

 

Hi liselotte ,

 

With a modification on the formula you gave, please try:

nr_Metallic_Accessories_2 =
VAR _model_uid =
    SELECTEDVALUE ( 'dim_Model'[Model UID] )
RETURN
    IF (
        MAX ( 'fact_Accessory'[Is_Metallic] ) = 1,
        CALCULATE (
            DISTINCTCOUNT ( 'fact_Accessory'[Accessory_UID] ),
            FILTER (
                ALLSELECTED ( 'fact_Accessory' ),
                'fact_Accessory'[Model UID] = _model_uid
                    && 'fact_Accessory'[Is_Metallic] = 1
            )
        )
    )

Result:

 

Best Regards,
Zhu
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.