topic Re: check if the specific value is in related tablecolumn and set to 1 in DAX Commands and Tips

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

jelleschuurman — Thu, 25 Mar 2021 15:38:50 GMT

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

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

jelleschuurman — Thu, 25 Mar 2021 17:43:37 GMT

The database looks like this:

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

Markus_Re — Mon, 29 Mar 2021 19:19:13 GMT

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!

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

jelleschuurman — Tue, 30 Mar 2021 15:06:49 GMT

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

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

Markus_Re — Tue, 30 Mar 2021 17:01:02 GMT

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

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

jelleschuurman — Tue, 30 Mar 2021 17:24:53 GMT

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 🙂

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

jelleschuurman — Tue, 30 Mar 2021 17:34:25 GMT

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 🙂

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

Markus_Re — Wed, 31 Mar 2021 10:25:08 GMT

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😀