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, 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.

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.

6 Replies

  • 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.

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    1 year ago

    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

  • Poojara_D12's avatar
    Poojara_D12
    Super 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