Forum Discussion

Aimeeclaird's avatar
Aimeeclaird
Icon for Helper IV rankHelper 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_data2a...
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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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.