Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
CALCULATE(
SUM(Table[Column X]),
FILTER(
Table,
Table[Column A] = "Renewal" ||
Table[Column B] = "Needs Alignment")
)
Proud to be a Super User!
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
Calculate( Sum(
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)
CALCULATE(
SUM(Table[Column X]),
FILTER(
Table,
Table[Column A] = "Renewal" ||
Table[Column B] = "Needs Alignment")
)
Proud to be a Super User!
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"
Something like:
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
@Anonymous Table[Column A] IN {"Renewal", "Needs Assignment"}
Proud to be a Super User!
That was so much easier than I was trying to make it, didn't even know about the Filter function, this worked perfectly, Thank you!
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.
Proud to be a Super User!
Hi KHorseman,
The CALCULATE/FILTER syntax you supplied really helped me with DAX statements in general.
Thanks,
Dan
Hi there
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?
Thx! Patrick
Total_ABE = CALCULATE( SUM(SAP[Value]); FILTER( SAP; SAP[Attribut] = "A" || SAP[Attribut] = "B" || SAP[Attribut] = "E" ) )
Proud to be a Super User!
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) ) )
Proud to be a Super User!
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.
HELP!
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.
Thanks,
Dan
@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.
Thanks!
Jemma
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"))
Hi @dtartaglia
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! 🙂
Jemma
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
102 | |
86 |