Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to Solution.
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!
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
The database looks like this:
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:
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?
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
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😀
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
10 | |
10 | |
9 | |
9 |
User | Count |
---|---|
20 | |
13 | |
12 | |
11 | |
8 |