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.

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! 🙂