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

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
JustinDoh1
Post Prodigy
Post Prodigy

How do I express this in DAX? (explicitly say when there is only total 1 row of data)

I am sharing my Pbix file here.

 

I am trying to set different output for case 1 & case 2.

JustinDoh1_3-1629507440922.png

 

case 1: for ClientID = 1 (John Doe):

Because this person only has Total only one row with Step=1 with Consent=Refused, output should be 0.

 

For the rest cases, like case 2 (Jane Doh) - if there is more than 1 total row beyond case 1: output should be default value of 1.

 

How can I explicitly say "if there is only one row of Step=1 & it's value has "Consent=Refused", output should be 0?

 

The measure is called "Step 1_0820".

JustinDoh1_4-1629508239559.png

I am guessing we might have to count the row(s) for row context first, and then look for the critiria? 

 

Currently, output for John Doe & Jane Doh are "", but somhow, total is 1.

When I tried with SUMX for this measure, output is more strange. 

JustinDoh1_0-1629509495476.png

 

 

Thank you so much for help.

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@JustinDoh1 Maybe:

Measure = 
  VAR __ClientID = MAX('Data'[ClientID])
  VAR __Table = FILTER(ALL('Data'),[ClientID]=__ClientID && [Step]=1)
  VAR __Count = COUNTROWS(__Table)
  VAR __Refused = COUNTROWS(FILTER(__Table,[Consent]="Refused"))
RETURN
  IF(__Count = 1 && __Refused = 1,0,1)


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

2 REPLIES 2
JustinDoh1
Post Prodigy
Post Prodigy

@Greg_Deckler Thank you so much, Greg. Appreciated for help. It worked and I learned another grammar of DAX.

Greg_Deckler
Community Champion
Community Champion

@JustinDoh1 Maybe:

Measure = 
  VAR __ClientID = MAX('Data'[ClientID])
  VAR __Table = FILTER(ALL('Data'),[ClientID]=__ClientID && [Step]=1)
  VAR __Count = COUNTROWS(__Table)
  VAR __Refused = COUNTROWS(FILTER(__Table,[Consent]="Refused"))
RETURN
  IF(__Count = 1 && __Refused = 1,0,1)


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
September Power BI Update Carousel

Power BI Monthly Update - September 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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