Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Aimeeclaird
Helper IV
Helper IV

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/EcITVcSSC6dNh9... 

 

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. 

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Community Champion
Community Champion

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.