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.
Hi there awesome community!
Ok... so here is my current roadblock. I have the following data:
Date (Column) | Project (C) | Resource (C) | Fee (C) - SUMS | Cumulative Fee (M) |
Also, I have concatenate keys to join with other tables including DayPrjID, PrjEmpID, and DayPrjEmpID. DayPrjEmpID would represent (at most) a single record of data and is the truly unique key.
Nevertheless, I'm struggling around Cumulative Fee. I've got a function working:
Cumulative Fee = Calculate(sumx(filter(allselected('CurrentTable'),[PrjEmpID] = max[PrjEmpID] && [Date]<=max([Date]))
What I want to know is how to modify the measure based off how it is calculated. In the above, I assume the user is calculating based off the lowest level (PrjEmpID for a given date). However if employee and/or project were not there, I would want it calculated by whatever group is being performed.
I'm hoping there is some function that can return true/false if it is being grouped/displayed.
Ideally, no other columns shown --- Cumulative Fee = the total of Fee
Date column shown --- Filter on all dates earlier
Projects column shown --- Filter on projects AND all dates earlier
Resource column shown --- Filter on project/resource AND all dates earlier
As always, much thanks!å
Solved! Go to Solution.
Hi @Anonymous
I doubt there's such function.
Normally we can manually modify the measure as ALLEXCEPT(table,column1) to ALLEXCEPT(table,column1,column2).
But to calculate the measure dynamically according to which column appear in the visual, no, i don't think it could be done.
I'd like to suggest you to create multiple measures and create a table contains measures' names then use this table as slicer.
Measure = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[name]))
Measure 2 = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[name],'Table'[type]))
Measure 3 = SWITCH(SELECTEDVALUE('Table (2)'[measure]),"measure",[Measure],"measure 2",[Measure 2])
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
I doubt there's such function.
Normally we can manually modify the measure as ALLEXCEPT(table,column1) to ALLEXCEPT(table,column1,column2).
But to calculate the measure dynamically according to which column appear in the visual, no, i don't think it could be done.
I'd like to suggest you to create multiple measures and create a table contains measures' names then use this table as slicer.
Measure = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[name]))
Measure 2 = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[name],'Table'[type]))
Measure 3 = SWITCH(SELECTEDVALUE('Table (2)'[measure]),"measure",[Measure],"measure 2",[Measure 2])
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |