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.
Hello!
I feel like this should be simple, but for some reason I am not able to come up with a solution that works. I am trying to calculate the green column named Total for Max Version using a dax statement in a calculated colum.
Here is an example of my dataset:
Basically, I want the value in the "Total Responses" Column only for the max Version of every GroupKey.
The Total Responses column is a calculated column using the following formula:
Total Responses = CALCULATE(SUM(Documents[Responses]),
ALLEXCEPT(Documents,Documents[GroupKey])
)
Is there a way to include a filter to retun only for the max version in this calculation so I don't need the other column? How can I best accomplish this?
Thanks!
Solved! Go to Solution.
@Anonymous
Try this
= IF ( [Version] = CALCULATE ( MAX ( Documents[Version] ), ALLEXCEPT ( Documents, Documents[GroupKey] ) ), CALCULATE ( SUM ( Documents[Responses] ), ALLEXCEPT ( Documents, Documents[GroupKey] ) ) )
Hi @Anonymous,
To what I can understand you simply want to sum the total responsed per Group correct?
If you add the GroupKey in a table visual and then the Responses and select the summarize you will get the values you need, the only questionis the groups without answers in this case just create the following measure:
Response measures = SUM(Documents[Responses]) + 0
This will return the following visual:
The aggregation level of the response totals will be based on the rows you add as context on your visuals
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi there!
So, I needed the sum to be in the correct row-ie: in the row of the max version number per group. These particular rows are going to be flagged and used in another calculation, if that makes sense. I probably will not use these sums in any visualizations, just in the data table.
Thanks!
Hi @Anonymous,
Not sure if I understand the reason behind it but you can redo the measure to this:
Response measures = IF ( SELECTEDVALUE ( Documents[version] ) = CALCULATE ( MAX ( Documents[version] ); FILTER ( ALL ( Documents[GroupKey] ); Documents[GroupKey] = MAX ( Documents[GroupKey] ) ); ALL ( Documents[version] ) ); CALCULATE ( SUM ( Documents[Responses] ) + 0; ALLEXCEPT ( Documents; Documents[GroupKey] ) ); BLANK () )
As you can see only on the max version you have values even when placing the version as a context
Not meaning to take any merit from @Zubair_Muhammad (a great datanaut and PBI expert) but just giving other ways of approaching the issue without having columns added to the model and additional weight to it.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@Anonymous
Try this
= IF ( [Version] = CALCULATE ( MAX ( Documents[Version] ), ALLEXCEPT ( Documents, Documents[GroupKey] ) ), CALCULATE ( SUM ( Documents[Responses] ), ALLEXCEPT ( Documents, Documents[GroupKey] ) ) )
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 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |