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.

Poojara_D12's avatar
Poojara_D12
Icon for Super User rankSuper User
1 year ago

Hi liselotte 

To create a table showing Model Name, Factory, Style Name, and Nr Metallic Accessories with a consistent count of metallic accessories for each Model Name regardless of Style Name, you’ll want to do the following in DAX:

  1. Calculate the Distinct Count of Metallic Accessories for each Model Name with Is_Metallic = 1.
  2. Remove Filters on Style Name so that Nr Metallic Accessories is not affected by the Style dimension.

Here’s a measure that should achieve this:

 

Nr_Metallic_Accessories = 
CALCULATE(
    DISTINCTCOUNT('fact_Accessory'[Accessory UID]),
    'fact_Accessory'[Is_Metallic] = 1,
    REMOVEFILTERS('dim_Style'[Style UID]) // Removes the Style filter to avoid interference
)

 

Create a visual table with:

  • Model Name, Factory, and Style Name from your dimension tables.
  • Nr Metallic Accessories from the measure we just defined.

This measure should produce the same count of metallic accessories for each Model Name across all styles. Let me know if this approach works!

 

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS