Forum Discussion

Aimeeclaird's avatar
Aimeeclaird
Helper IV
5 years ago
Solved

Count based on multiple conditions

Apologies if my subject is unclear, I wasn't sure on the language to use. 

 

I have a set of data similar to this example: https://data2actionltd-my.sharepoint.com/:x:/g/personal/aimee_laird_data2action_co_uk/EcITVcSSC6dNh9gtbim7_5UBayIfCx0U3OzSR5MZxGZh3w?e=bEoLnE 

 

I think I need to do this as a calculated column rather than a measure, however open to your advice.

 

I am looking at the how many items were sold to a property. Depending on the item, is the sale classed as a Single or a Dual sale. 

E.g.

Water + Bread = Dual

Water + Sugar = Single

Water + Bread + Sugar = Dual

Water = Single

Bread = Single

 

In my dashboard, I want to show:

Number of Multi sales WTD, MTD and YTD

Number of Single sales WTD, MTD, and YTD 

 

Help please! i've tried all sorts but can't make sense of a solution. 

column calculation
COUNTIF
DAX
Measures
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    Aimeeclaird So try something like:

    Type = 
    VAR __Table = FILTER('Table (13)',[Address] = EARLIER([Address]))
    VAR __Items = DISTINCT(SELECTCOLUMNS(__Table,"__Item",[Item]))
RETURN
    SWITCH(TRUE(),
        COUNTROWS(__Items) = 1,"Single",
        "Water" IN __Items && "Bread" IN __Items,"Dual",
        "Single"
    )

    I mocked this up in attached PBIX (Table (13)) below sig.

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Aimeeclaird So try something like:

    Type = 
    VAR __Table = FILTER('Table (13)',[Address] = EARLIER([Address]))
    VAR __Items = DISTINCT(SELECTCOLUMNS(__Table,"__Item",[Item]))
RETURN
    SWITCH(TRUE(),
        COUNTROWS(__Items) = 1,"Single",
        "Water" IN __Items && "Bread" IN __Items,"Dual",
        "Single"
    )

    I mocked this up in attached PBIX (Table (13)) below sig.