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! 🙂