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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply

check if the specific value is in related tablecolumn and set to 1

Hello,

 

I'm relatively new to DAX.

I have 2 related tables (Azure SQL database). Appointments and Actions. They are connected via AppointmentID.

 

Every Appointment has 1 or more Actions. What I want to do is, If one or more Actions have the value "C90", set the value of _noShowAppointment to 1.

 

I tried with different types of formulas but I wont get the good result. What is the correct formula to do this? Am I even close to the correct formula? I tried among other things this formulas (column is made in Appointments table): 

_noShowAppointment = CALCULATE( COUNTROWS(actions),FILTER(actions,action[tariefCode]="C90"))
_noShowAppointments= if(SEARCH("C90",LOOKUPVALUE(action[tariefCode],action[appointmentId],appointment[appointmentid]),1,0),1,0)
_noShowAppointments = COUNTX(action,action[tariefCode]="C90")


Thank you for your help. 

Jelle

 

 

2 ACCEPTED SOLUTIONS

Hi @jelleschuurman 

 

here is how i would solve this:

 

_noShowAppointment = 

IF(COUNTX(FILTER(RELATEDTABLE(Actions), Actions[Tariefcode] = "C90"), Appointments[Appointmentid]) > 0, 1, 0)

Best regards,

 

Markus

Did this answer your question? Mark my post as a solution!

View solution in original post

Hi @jelleschuurman,

 

for this to work you would have to add a unique Index-Column in PowerQuery, otherwise DAX can´t evaluate this case.

(https://sqlskull.com/2020/09/08/create-index-column-using-a-power-query/

 

After adding the index column my table looks like this:

 

Markus_Re_0-1617123558146.png

To create the column you are looking for i used this:

 

 

YourColumn = 

var _currentID = Dossier[DossierID]
var _currentDate = Dossier[Date]
var _currentIndex = Dossier[Index]

RETURN
IF(COUNTX(FILTER(Dossier, Dossier[DossierID] = _currentID && Dossier[Date] = _currentDate && Dossier[Index] < _currentIndex), Dossier[Index]) > 0, 0, 1)

 

 

Best regards,

 

Markus

View solution in original post

7 REPLIES 7

The database looks like this: 

Knipsel.JPG

Hi @jelleschuurman 

 

here is how i would solve this:

 

_noShowAppointment = 

IF(COUNTX(FILTER(RELATEDTABLE(Actions), Actions[Tariefcode] = "C90"), Appointments[Appointmentid]) > 0, 1, 0)

Best regards,

 

Markus

Did this answer your question? Mark my post as a solution!

Thank you. I have another similar question, but then for the same table. 

 

This is the database:

Knipseld.PNG

Is it possible to calculate this? 

Thank you 

 

I tried this measure; It seems to give the correct result. Is this the best way to do this?

 

if(CALCULATE(SUM('Table'[DossierID]), ALLSELECTED('Table'[Date])),1,0)
 
Thank you 🙂 

Hi @jelleschuurman,

 

for this to work you would have to add a unique Index-Column in PowerQuery, otherwise DAX can´t evaluate this case.

(https://sqlskull.com/2020/09/08/create-index-column-using-a-power-query/

 

After adding the index column my table looks like this:

 

Markus_Re_0-1617123558146.png

To create the column you are looking for i used this:

 

 

YourColumn = 

var _currentID = Dossier[DossierID]
var _currentDate = Dossier[Date]
var _currentIndex = Dossier[Index]

RETURN
IF(COUNTX(FILTER(Dossier, Dossier[DossierID] = _currentID && Dossier[Date] = _currentDate && Dossier[Index] < _currentIndex), Dossier[Index]) > 0, 0, 1)

 

 

Best regards,

 

Markus

Thank you. I just saw your comment and it differs from the 'solution' I thought I found (previous post).  It also seems to work (but I only used it on a very small table). I'm interessested what i'm missing 🙂

 

It depends on your usecase. Sometimes I prefer to use a calculated column instead of a Measure, but if your Formula works for you, thats great too😀

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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