Im pretty new to writing DAX, and am trying to do something that would be simple in excel, but I can't seem to create it in Power BI.
I'm trying to create a custom measure that says:
Calculate the Sum of Column X IF Column A = "Renewal" OR Column B = "Needs Alignment".
Very simple measure, but I can't seem to get the OR statement in there, and I can't use the filters in the Calculate function because once I put "Column X = "Renewal", it filters out all of the "Column B = "Needs Alignment".
Any ideas? Any help would be greatly appreciated.
Solved! Go to Solution.
What if you have a multiples tables that need produce a Sum only if they have a value above 0 for example and I know this code isnt correct but im trying to explain
if Column 1 > 0,
if Column 2 > 0,
if Column 3 > 0)
How would I write something in the proper format in DAX
since i cant use if statements
This is what im trying to convert into DAX
SUM(iif((Fields.ADDTL_INST > 0
Or Fields.ADDTL_REF > 0
Or Fields.EXCISE_TAX > 0
Or Fields.RECORDING_FEES > 0
Or Fields.NON_STANDARD_FEES > 0)
Lets say both values "renewal" and "needs assignment" are in column A,
is there a way to mention both values in the filter (or anywhere else) without haviing the mention Table [column a] twice?
so instead of:
Table[Column A] = "Renewal" ||
Table[Column A] = "Needs Alignment"
Table[Column A] = ("Renewal" || "Needs Alignment")
tried creating a VAR to return into my logical function but I dont get it right somehow
Any ideas? @KHorseman
CALCULATE(<expression>, FILTER( <table>, <table[column] = condition>)) is probably the most generally useful pattern to learn in DAX. The vast majority of measures I write either follow this pattern or contain a part that follows this pattern.
Thanks for this solution - it guided me a step further with a filter challange
i'm currently struggling with adding multiple filters:
POS Attribut Value 1 A 10 2 B 200 3 C 3000 4 D 40000 5 E 500000 6 A 10 7 B 200 8 C 3000 9 D 40000 10 E 500000
I summed the total of all SAP[Value] where [Attribute] is "A":
Total_AB = CALCULATE(SUM(SAP[Value]); FILTER( SAP; SAP[Attribut] = "B") )
But I need to Sum the total of all SAP[Value] where [Attribute] is "A", "B" and "E" - but the filter functions allow only 2 statements. Does someone has an idea how to solve this with DAX?
Total_ABE = CALCULATE( SUM(SAP[Value]); FILTER( SAP; SAP[Attribut] = "A" || SAP[Attribut] = "B" || SAP[Attribut] = "E" ) )
Any idea how to get this to work using the example above?
(Attribut <> "E" AND Status < 4 ) OR (Attribut = "B" AND Status = 3)
POS Attribut Status Value 1 A 1 10 2 B 3 200 3 C 1 3000 4 D 4 40000 5 E 2 500000 6 A 4 10 7 B 1 200 8 C 2 3000 9 D 1 40000 10 E 4 500000
@potapthe syntax is the same. You can use parentheses. && is the AND operator, || is the OR operator.
CALCULATE( SUM(TableName[Value]), FILTER( TableName, (TableName[Attribut] <> "E" && TableName[Status] < 4) || (TableName[Attribut] = "B" && TableName[Status] = 3) ) )
It was really helpful.
You mentioned that this is one of your favourite DAX patterns. Could you please share your post or article with your other favourite DAX patterns?
@vyacheslavg I'm having trouble with this unless I'm going mad I have created a very simple calculate DAX measure with an or filter and it's not working properly.
I know the result is:
1001 = 36
1002 = 13
both = 49
My measure is:
Members follow up or regular appointment = CALCULATE(DISTINCTCOUNT('Appointments'[Member]), FILTER( 'Appointments', 'Appointments'[Type]="1001"||'Appointments'[Type]="1002"))
And the measure result is 36.
Hi @vyacheslavg DISTINCTCOUNT is expecting to return a single value in a calculated measure. What is the end result/visual you need the information in? This can easily be done in a table or other visuals without DAX.
@dtartaglia Hi, so the distinctcount is counting unique instances of a member id, where the appointment type for the same row is 1001 or 1002.
This is because there are hundreds of appointments for each member and I want to count unique members who have had at least one appointment.
I'm using a very similar DAX measure in Desktop with slightly different syntax. I seem to get the correct number (created around 50 records). Please let me know if I'm missing something:
Members follow up or regular appointment = CALCULATE(DISTINCTCOUNT(Appointments[Members]), FILTER(Appointments, Appointments[Type] = "1001" || Appointments[Type] = "1002"))
No, you're not missing something - I was. Clearly, I have members with an appointment with BOTH codes hence it's not adding the 13 and 36 exactly. I was expecting 49 but your example shows me what is actually happening here.
Thank you so much, I really appreciate it! 🙂
Click here to learn more about the September 2022 updates!
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.
Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!