Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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😀
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |