Calculating unique combination from different tables

[Nicpet0]

Hi Community

I am facing an issue in Power Bi with a DAX measure i am trying to create. (See below the pasted code, as it looks currently)

I am trying to count each occurence of the unique combination of [Variant Item No], [Variant Code] and [Sales Person Name] whenever there as a sale where the Gross Margin % is negative. The first to mentioned fields comes from the same table 'Variant' while Sales Person comes from its own table. They are indirect linked through a 'Sales' table. 

What i am looking for is the most memory efficient DAX code that can achieve what i described above. Whenever i just have the combination of Variant Item No and Variant Code it runs fine, but it is when i also want to combine a field from another table where it becomes increasingly more taxing for it to run. 

Any advide is helpful, thank you.

Count of Negative Margin =

VAR Groups =

    SUMMARIZECOLUMNS(

        'Variant'[Variant Item No],

        'Variant'[Variant Code],

        'Sales Person'[Sales Person Name]

    )

RETURN

    COUNTROWS(

        FILTER(

            Groups,

            CALCULATE( [Gross Margin 2 %] ) < 0

        )

    )

[johnt75 · Accepted solution]

I think your code just needs a little tweak

Count of Negative Margin =
VAR Groups =
    SUMMARIZECOLUMNS (
        'Variant'[Variant Item No],
        'Variant'[Variant Code],
        'Sales Person'[Sales Person Name],
        "@gross margin", [Gross Margin 2 %]
    )
RETURN
    COUNTROWS ( FILTER ( Groups, [@gross margin] < 0 ) )

By including the measure in the SUMMARIZECOLUMNS you will only return combinations of item no, code and sales person which have made sales, rather than all possible combinations which your original code would do. You can then filter that for values below 0.

[johnt75 · Accepted solution]

I think your code just needs a little tweak

Count of Negative Margin =
VAR Groups =
    SUMMARIZECOLUMNS (
        'Variant'[Variant Item No],
        'Variant'[Variant Code],
        'Sales Person'[Sales Person Name],
        "@gross margin", [Gross Margin 2 %]
    )
RETURN
    COUNTROWS ( FILTER ( Groups, [@gross margin] < 0 ) )

By including the measure in the SUMMARIZECOLUMNS you will only return combinations of item no, code and sales person which have made sales, rather than all possible combinations which your original code would do. You can then filter that for values below 0.

[Nicpet0 · Accepted solution]

Thanks, that did the trick!