Forum Discussion

liselotte's avatar
liselotte
Advocate 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.

liselotte's avatar
liselotte
Advocate I
1 year ago

Hi Zhu,

Thank you for your help and sorry for my late response. Your suggested query seems to work well for the rows with Sum of Is_Metallic =1, but I expected that the second row in your screenshot should have nr_Metallic_Accessories_2=2 too. Is there any way to do that? Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

    • liselotte's avatar
      liselotte
      Advocate I
      1 year ago

      That works like a charm. Thank you very much! 🙂