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

[jelleschuurman]

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

[Markus_Re · Accepted solution]

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!

[jelleschuurman]

The database looks like this: 

[Markus_Re · Accepted solution]

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!

[jelleschuurman]

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

This is the database:

Is it possible to calculate this? 

Thank you 

[jelleschuurman]

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 🙂 

[Markus_Re · Accepted solution]

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:

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

[jelleschuurman]

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 🙂

[Markus_Re]

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😀