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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors