DAX multiple iterator issue

[Anonymous]

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?

[OwenAuger]

@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?

[Anonymous]

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. 

[Anonymous · Accepted 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