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 have a very strange behaviour which I hope someone on the forum can help me with.
I have a measure that consists of three nested SUMX () functions.
which looks something like this
SUMX ( VALUES ( Table[Column1] ), SUMX ( CALCULATETABLE ( [Table Expression], Table[Type] = "A" ), SUMX ( CALCULATETABLE ( [A different Table Expression], Table[Type] = "B" ), [Measure] ) ) )
This measure is returning blank however if I swap all the SUMX for CONCATANATEX I get the value you want but as a string. I can even Change it to a SUMX ( VALUE ( CONCATANATEX ( SUMX () ) ) ) and it will work in some scenarios. I have already tried using VALUE () within the SUMX iterators to force type conversion even though that shouldnt be required and it did not solve the issue.
Has anyone run into a similar situation where the logic works in CONCATANATEX but not in anyother iterator?
Solved! Go to Solution.
I managed to solve the issue by moving the filters to the report page and then just using a calculate table with no extra filters on the second SUMX
@Anonymous
That seems strange - I can't say I've seen this issue before.
If you simply replace all occurrences of SUMX with CONCATENATEX, then exactly the same tables are being iterated and the final concatenated string should contain the values being summed in the original measure.
Could you post a santised pbix file exhibiting the issue?
Hello Owen,
I have uploaded the file here
https://www.dropbox.com/s/8vi33va0778q22k/SUMX%20Issue.pbix?dl=0
In the file I have created an example table
Id | Site | Product | Downtime start | Downtime end |
1 | Site A | Primary Product | 2019-01-01 03:00 | 2019-01-01 04:00 |
2 | Site A | Backup Product | 2019-01-01 02:00 | 2019-01-01 05:00 |
3 | Site A | Backup Product | 2019-01-02 03:00 | 2019-01-02 06:00 |
Then I have five measures.
The first is distinct product count which is what you think it is.
The second is a simple SUMX that works out the difference in seconds for all the rows in context and sums them.
The rest of the measures are all variations of the same pattern. What I want is a measure that will calculate for the site in context what is the total amount of time where both the primary and backup were down.
The three versions of this just change the iterator, there is one with SUMX which returns BLANK, there is one with CONCATENTATEX which returns 36000 which is 3600 appended with 0 which is what I would expect. The final measure is a combination of SUMX and CONCATENTATEX which uses the VALUE function. This one reuturns a correct numeric result of 3600. However this is not a scaleable solution it just works in this subset of data.
I managed to solve the issue by moving the filters to the report page and then just using a calculate table with no extra filters on the second SUMX
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 |
---|---|
105 | |
97 | |
80 | |
66 | |
62 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |