topic Measure excluding row context in DAX Commands and Tips

Measure excluding row context

liselotte — Thu, 14 Nov 2024 20:16:53 GMT

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.

Re: Measure excluding row context

PhilipTreacy — Thu, 14 Nov 2024 23:06:44 GMT

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

Re: Measure excluding row context

Poojara_D12 — Fri, 15 Nov 2024 05:02:04 GMT

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:

Calculate the Distinct Count of Metallic Accessories for each Model Name with Is_Metallic = 1.
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

Re: Measure excluding row context

Anonymous — Fri, 15 Nov 2024 06:44:28 GMT

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.

Re: Measure excluding row context

liselotte — Thu, 21 Nov 2024 19:18:29 GMT

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.

Re: Measure excluding row context

Anonymous — Mon, 25 Nov 2024 06:37:33 GMT

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.

Re: Measure excluding row context

liselotte — Mon, 25 Nov 2024 08:22:36 GMT

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