Measure excluding row context

[liselotte]

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, Style Name

- fact table fact_Accessory with columns Model UID, Style UID, Accessory_UID, Is_Metallic, Year, Price

I want to create a visual table of columns Model Name, Factory, Style Name, Nr Metallic Accessories which is Distinct Count of Accessory_UID with Is_Metallic=1 for Model Name. I expect that Nr Metallic Accessories is the same for each Model Name regardless Style Name. 

I have tried these queries but they don't work:

nr_Metallic_Accesories_1 = CALCULATE(
    SUMX(
        SUMMARIZE('fact_Accessory','fact_Accessory'[Accessory UID],'fact_Accessory'[Is_Metallic])
        ,[Is_Metallic]
    )
    ,REMOVEFILTERS('dim_Style')
    ,FILTER(
        'fact_Accessory'
        ,'fact_Accessory'[Is_Metallic]=1
    )
)

nr_Metallic_Accessories_2 = 
var _model_uid = SELECTEDVALUE('dim_Model'[Model UID])
return
CALCULATE(
	DISTINCTCOUNT('fact_Accessory'[Accessory UID]),
    FILTER( 
        ALLSELECTED('fact_Accessory')
        ,'fact_Accessory'[Model UID] = _model_uid
        && 'fact_Accessory'[Is_Metallic]=1
    ) 
)

Could anyone help me with this, please? Thank you.

[v-linhuizh-msft]

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]

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.

[v-linhuizh-msft · Accepted solution]

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]

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

[Poojara_D12]

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

[PhilipTreacy]

@liselotte 

Can you supply sample data please, or your PBIX file.  You'll get help quicker if we don't have to recreate the data for all the tables.

Regards

Phil