Adding column from seperate table to unique combination

[Nicpet0]

Hi Community

i am facing an issue with updating my DAX measure. Below you will find the current code which intends to count the number of unique combinations of variant item no and variant code where there margin is between 10-25%, and it works just fine for the current setup in my report. However, the business requirements have changed a bit, so it also needs to contain the combination of sales person. So now a three part unique combination. Both Variant item no and code is stored in a table called "Variant", though the information for sales person is stored in a seperate table, and i cant seem to figure out how to perform this task with my code today, since it wont allow me to add more than one table reference (Variant) and so i cant add Sales person like this:

Variant[Variant Item No],

Variant[Variant Code],
Sales Person[Sales Person Name]

Current code:
# Count of Margin between 10-25% =

CALCULATE(

    COUNTROWS(

        SUMMARIZE(

            FILTER(

                Variant,

                NOT(ISBLANK([Invoiced Amount Incl All Charges])) &&

                [Gross Margin 2 %] > 0.10 && [Gross Margin 2 %] <= 0.25

            ),

            Variant[Variant Item No],

            Variant[Variant Code]

        )

    )

)

Im not sure if this matters in this context, but my connection is a tabular model through analysis services. 

Any ideas how to update my code or perhaps change it entirely based on my requirements?

Thanks!

[v-denglli-msft · Accepted solution]

Thanks for the reply from DataNinja777, please allow me to provide another insight.
Hi @Nicpet0 ,

I am not sure how your dataset is designed, here is my data model.

In my model, the 'Variant' table and the 'Sales Person' table are indirectly connected through the intermediate table 'Sales'.

Therefore, you can try the following measure.

# Count of Margin between 10-25% = 
VAR tb =
    ADDCOLUMNS (
    Variant,
    "SalesPerson", CALCULATE ( MAX ( 'SalesPerson'[Sales Person Name] ),RELATEDTABLE(Sales))
    )
RETURN
    CALCULATE (
        COUNTROWS (
            SUMMARIZE (
                FILTER (
                    tb,
                    NOT ( ISBLANK ( [Invoiced Amount Incl All Charges] ) )&& 
                    [Gross Margin 2 %] > 0.10 && [Gross Margin 2 %] <= 0.25
                ),
                Variant[Variant Item No],
                Variant[Variant Code],
                [SalesPerson]
            )
        )
    )

The 'tb' variable creates a virtual table that adds a corresponding SalesPerson to each 'Variant Item No' row in the 'Variant' table.

The virtual table is as follows.

The result of the measure is as follows.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nicpet0]

@DataNinja777 

Yeah they are connected indirectly. Neither of the two options will allow me to use 

'Sales Person'[Sales Person Name]

It just simply cant recognize the parameter in the code. Can you elaborate of the indirect relationships require adjustments in filter propogation?

[DataNinja777]

Hi @Nicpet0 ,

Your challenge arises because the Variant table contains both Variant Item No and Variant Code, while Sales Person information is in a separate table. The SUMMARIZE function can only reference columns from a single table, which is why you're unable to add Sales Person Name directly. To resolve this, you can use ADDCOLUMNS to bring in Sales Person Name from the related table using RELATED(), assuming a direct relationship exists between Variant and Sales Person.

# Count of Margin between 10-25% =
CALCULATE(
    COUNTROWS(
        SUMMARIZE(
            FILTER(
                ADDCOLUMNS(
                    Variant,
                    "SalesPerson", RELATED('Sales Person'[Sales Person Name])
                ),
                NOT(ISBLANK([Invoiced Amount Incl All Charges])) &&
                [Gross Margin 2 %] > 0.10 && [Gross Margin 2 %] <= 0.25
            ),
            Variant[Variant Item No],
            Variant[Variant Code],
            [SalesPerson]
        )
    )
)

This code first uses ADDCOLUMNS to extend the Variant table by adding the Sales Person Name using RELATED(). Then, it applies the necessary filters on margin values and non-blank invoiced amounts. SUMMARIZE groups the data by Variant Item No, Variant Code, and Sales Person Name, and COUNTROWS counts the unique combinations.

If there's no direct relationship between Variant and Sales Person but they are connected through another table, TREATAS can be used to establish the relationship dynamically.

# Count of Margin between 10-25% =
VAR FilteredData =
    FILTER(
        Variant,
        NOT(ISBLANK([Invoiced Amount Incl All Charges])) &&
        [Gross Margin 2 %] > 0.10 && [Gross Margin 2 %] <= 0.25
    )

RETURN
CALCULATE(
    COUNTROWS(
        SUMMARIZE(
            FilteredData,
            Variant[Variant Item No],
            Variant[Variant Code],
            'Sales Person'[Sales Person Name]
        )
    ),
    TREATAS(VALUES('Sales Person'[Sales Person Name]), Variant[Sales Person ID])  
)

In this version, the TREATAS function helps bridge the relationship when RELATED() isn’t applicable. It ensures that the Sales Person Name values align with the Variant table, allowing the count of unique combinations to reflect the business requirement accurately. If your data model allows, confirm that the relationships are properly defined, as indirect relationships can sometimes require adjustments in filter propagation.

Best regards,