Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I try to create a new measure which does the sum based on two conditions: COURSE_FTE_SCH is the table name, [Term],[Course_Level] and [Fundable_SCH ]are the columns within this table. How could I combine these conditions into one dax formula? Thanks!
If COURSE_FTE_SCH[Term] in {"Spring","Fall"} and COURSE_FTE_SCH[Course_Level] in {"Lower UG","Upper UG"} Then
If COURSE_FTE_SCH[Term] in {"Spring","Fall"} and COURSE_FTE_SCH[Course_Level] in {"Graduate"} Then
If COURSE_FTE_SCH[Term] in {"Summer"} and COURSE_FTE_SCH[Course_Level] in {"Lower UG","Upper UG"} Then
If COURSE_FTE_SCH[Term] in {"Summer"} and COURSE_FTE_SCH[Course_Level] in {"Graduate"} Then
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @AllisonKennedy thanks for your explanation. This does help me understand how the formula works. Your formula works. but when I use the matrix visualization with Modality on Rows, TERMID on Columns, and FTE on Values I have the blank results as shown below. I do have the Term filter on this page.
What I want to achieve is some matrix table like this. 201508/201608... are the TERMIDs grouped under the column "Term" which includes Spring, Fall, Summer.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thanks @AllisonKennedy ! I followed your advice and put Course_Level on Rows, TERMID on Columns, FTE on Values. I am able to get a matrix visualization like this
What I want to do is a matrix visualization like the following: with Modality on Rows, TERMID on Columns, FTE on Values. I have Term, Campus, College etc. on the filters so the FTE changes dynamically when I click across different filters. How should I modify the formula?
Here is a screenshot of my dataset. Thank you!
Hi @xliu1 ,
Create slicers for Term, Campus, College etc instead of using filters.
It's better to share you sample data as tables instead of pictures.
Best Regards,
Jay
Thanks for your suggestiom. I do not see an option to upload file here. And my work place does not allow file sharing using one drive or Dropbox etc. Are there any other options available for new members to share file? Thanks.
HI @xliu1
I would use the below formula and plot it in the matrix chart with rows as Term and Course level.
FTE = CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH]) * if(COURSE_FTE_SCH[Course_Level] ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))
Did I resolve your issue? Mark my post as a solution!
Appreciate your Kudos, Press the thumbs up button!!
Regards,
Pranit
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @xliu1
FTE = CALCULATE(SUM(SELECTEDVALUE(COURSE_FTE_SCH[Fundable_SCH])) * if(SELECTEDVALUE(COURSE_FTE_SCH[Course_Level]) ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))
IF you get any more error add the SELECTEDVALUE function as I have added for the error above.
Did I resolve your issue? Mark my post as a solution!
Appreciate your Kudos, Press the thumbs up button!!
Regards,
Pranit
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @xliu1
I would modify it to
FTE = CALCULATE(SUMX(SELECTEDVALUE(COURSE_FTE_SCH[Fundable_SCH])) * if(SELECTEDVALUE(COURSE_FTE_SCH[Course_Level]) ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))
Let me know if you still have an error.
Hope this resolves your issue? Mark my post as a solution!
I am trying to help you. Appreciate your Kudos, Press the thumbs up button!!
Regards,
Pranit
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @xliu1
I would modify it to
FTE = CALCULATE(SUMX(COURSE_FTE_SCH,SELECTEDVALUE(COURSE_FTE_SCH[Fundable_SCH])) * if(SELECTEDVALUE(COURSE_FTE_SCH[Course_Level]) ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))
OR
FTE = CALCULATE(SUMX(COURSE_FTE_SCH,COURSE_FTE_SCH[Fundable_SCH]) * if(COURSE_FTE_SCH[Course_Level] ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))
If this doesn't work, I would try once with removing the SELECTEDVALUE function one by one.
May be a better idea to reply back with the .PBIX file if further debugging required.
Hope this resolves your issue? Mark my post as a solution!
I am trying to help you. Appreciate your Kudos, Press the thumbs up button!!👍
Regards,
Pranit
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @pranit828 thank you very much for helping me troubleshooting this. Your formula syntax works fine this time. However, when I drag it to the visualization, it does not work the way I expected.
I want to do a calculation of FTE by term and modality. I have an example matrix table "FTE by Instruction Modality." I am not sure how I can attach pbix file to this blog.
Hi @xliu1
FTE = CALCULATE(SUMX(COURSE_FTE_SCH,COURSE_FTE_SCH[Fundable_SCH]) * if(COURSE_FTE_SCH[Course_Level] ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[modality]))
In the formula I have changed the ALLEXCEPT function section as above to calculate by modality instead.
ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[modality])
For some new community members, they don't have the permission to upload .pbix files. You may need to share file links via DropBox, One Drive, Drive or any other tool.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Thanks for your help. 👍
Hi @xliu1
Did my solution work?
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @pranit828 , no. I am still figuring why your formula works but when I put it to Values it returned blank results.
I have a matrix visualization with TERMID on Rows, Modality on Columns and FTE on Values. I have Term as a filer on this page. 201508/201608... are TERMIDs grouped under Term which includes Spring, Summer, Fall. what I like to achieve is something like this matrix table
something like this. Note that you are missing the "or else" option, and using "in" with single values is a bit rich.
FTE = SWITCH(TRUE(), COURSE_FTE_SCH[Term] in {"Spring","Fall"} && COURSE_FTE_SCH[Course_Level] in {"Lower UG","Upper UG"},CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/15)*0.375,
COURSE_FTE_SCH[Term] in {"Spring","Fall"} && COURSE_FTE_SCH[Course_Level] in {"Graduate"},
CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/12)*0.375,
COURSE_FTE_SCH[Term] in {"Summer"} && COURSE_FTE_SCH[Course_Level] in {"Lower UG","Upper UG"},
CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/10)*0.25,
COURSE_FTE_SCH[Term] in {"Summer"} && COURSE_FTE_SCH[Course_Level] in {"Graduate"},
CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/8)*0.25 )
I also hope the numbers you provided were just samples. In this sample scenario the semester doesn't matter at all. You could have rewritten the measure as
FTE = SUM(COURSE_FTE_SCH[Fundable_SCH]) * if(COURSE_FTE_SCH[Course_Level] ="Graduate",0.03125,0.025)
Hi @lbendlin thanks for your reply. I tried your formula and got this error message: Any idea what is wrong? Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |