Forum Discussion

Nicpet0's avatar
Nicpet0
Frequent Visitor
1 year ago
Solved

Calculating unique combination from different tables

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
        )
    )
"DAX " "DAX help" 'Need help'
DAX
  • johnt75's avatar
    johnt75
    1 year ago

    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.

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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's avatar
      Nicpet0
      Frequent Visitor
      1 year ago

      Thanks, that did the trick!