topic Re: Calculating unique combination from different tables in DAX Commands and Tips

Calculating unique combination from different tables

Nicpet0 — Thu, 14 Aug 2025 08:02:46 GMT

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

)

Re: Calculating unique combination from different tables

johnt75 — Thu, 14 Aug 2025 08:28:53 GMT

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.

Re: Calculating unique combination from different tables

Nicpet0 — Thu, 14 Aug 2025 10:13:36 GMT

Thanks, that did the trick!