totals for products of measures, multiple contexts (DAX help)

[dedelman_clng]

This one is complicated, so please bear with me.  To start I have a complex data model due to having a few many-to-many relationships that need to be resolved

The main fact table is "IT Outages", but

- one outage can affect multiple CIs, and one CI can have multiple outages (hence, Outage CI)

- one CIs can affect multiple applications, and one application is served by multiple CIs (hence, CIA)

I can calculate a single event total outage by taking the single outage duration and multiplying by the number of apps that were effected:

Total App Outage = 
CALCULATE (
    CALCULATE (
        SUM ( 'IT Outages'[Business Time] ),
        'Outage CI',
        NOT ( 'IT Outages'[Exclude] )
    ),
    CIA
)

Total Apps Affected = 
CALCULATE (
    CALCULATE (
        COUNTROWS ( VALUES(CIA[Application] )
    ),
    FILTER('Outage CI', NOT ( RELATED('IT Outages'[Exclude] )))
))

Total Outage = [Total App Outage] * [Total Apps Affected]

This works well for each point in time, but not in aggregate.  [Total Outage], when not filtered by Date, give a much higher number than it should.  Same with the following formula (which is basically the DAX for the first 2 measures replicated into a single code):

Running Downtime = 
TOTALYTD(
 CALCULATE (
    CALCULATE (
        SUM ( 'IT Outages'[Business Time] ),
        'Outage CI',
        NOT ( 'IT Outages'[Exclude] )
    ),
    CIA
) * 
 CALCULATE (
    CALCULATE (
        COUNTROWS ( VALUES(CIA[Application] )
    ),
    FILTER('Outage CI', NOT ( RELATED('IT Outages'[Exclude] )))
)),
 DateTab[Date])+0

I have tried to summarize the data below. Yellow is a wrong value, green is correct. The far right column is the expected result.

Essentially I'm trying to do the summation over a product of two measures.

Any assistance would be appreciated.

Thanks,

David Edelman

ETA: Sample data provided in a reply below

[v-yulgu-msft · Accepted solution]

Hi @dedelman_clng,

I could not test your measures with the provided sample data, as column [Exclude] cannot be found in table 'IT Outages'.

Please try to create measures like below:

Total Outage = [Total App Outage] * [Total Apps Affected]

Total Outage2=sumx('IT Outages',[Total Outage])

Running Downtime =
CALCULATE (
    [Total Outage],
    FILTER (
        ALLSELECTED ( 'IT Outage' ),
        'IT Outage'[Date] <= MAX ( 'IT Outage'[Date] )
    )
)

Best regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @dedelman_clng,

I could not test your measures with the provided sample data, as column [Exclude] cannot be found in table 'IT Outages'.

Please try to create measures like below:

Total Outage = [Total App Outage] * [Total Apps Affected]

Total Outage2=sumx('IT Outages',[Total Outage])

Running Downtime =
CALCULATE (
    [Total Outage],
    FILTER (
        ALLSELECTED ( 'IT Outage' ),
        'IT Outage'[Date] <= MAX ( 'IT Outage'[Date] )
    )
)

Best regards,

Yuliana Gu

[dedelman_clng · Accepted solution]

Hi @v-yulgu-msft

The code you gave me got me in the right direction.  I also needed to have any day without an outage show the current YTD total, so I added another CALCULATE around the date table:

Running Down Time =
CALCULATE (
    CALCULATE (
        [Total Outage 2],
        FILTER (
            ALL ( 'IT Outages' ),
            'IT Outages'[Date of Outage] <= MAX ( 'IT Outages'[Date of Outage] )
        )
    ),
    FILTER ( ALL ( DateTab ), DateTab[Date] <= MAX ( DateTab[Date] ) )
)

Thanks!

David

[dedelman_clng]

@v-yulgu-msft -

Apologies for leaving out the code.  Here it is below.  I will try your suggestion as well and report back.

Exclude = IF('IT Outages'[Business Impact] = "false", TRUE(), FALSE())

David

[dedelman_clng]

Sample data (date is a standard CALENDAR table) :

IT Outages:

Title	Description of Outage	Date of Outage	Total Time	Business Time	Business Impact	E&C Impact	Affected System
49	Outage 1	1/30/2018	15	15	TRUE	TRUE	Server
50	Outage 2	4/26/2018	3	3	TRUE	FALSE	Network
51	Outage 3	4/24/2018	18	18	TRUE	TRUE	Network
52	Outage 4	5/12/2018	720	720	FALSE	FALSE	Server
53	Outage 5	5/21/2018	17	17	TRUE	TRUE	Network
54	Outage 6	5/31/2018	681	81	TRUE	TRUE	Network
55	Outage 7	6/8/2018	5	5	TRUE	FALSE	Network
56	Outage 8	6/21/2018	35	35	TRUE	FALSE	Server
57	Outage 9	6/25/2018	72	72	TRUE	FALSE	Server
58	Outage 10	7/6/2018	508	508	TRUE	FALSE	Server
59	Outage 11	7/10/2018	16	16	TRUE	TRUE	Network

Outage CI

Title	Affected CI(s)
49	CI29
50	CI56
51	CI56
52	CI22
52	CI40
53	CI40
54	CI22
54	CI40
55	CI22
56	CI18
57	CI11
57	CI18
57	CI19
58	CI32
59	CI28

CI

Infrastructure Instance	SLA Type
CI11	SLA11
CI18	SLA18
CI19	SLA19
CI22	SLA22
CI28	SLA28
CI29	SLA29
CI32	SLA32
CI40	SLA40
CI56	SLA56

CIA

Application	Infrastructure Instance
App1	CI40
App2	CI40
App3	CI28
App3	CI29
App4	CI22
App5	CI32
App5	CI40
App6	CI18
App6	CI19
App6	CI40
App7	CI40
App8	CI11
App8	CI40
App9	CI40
App10	CI40
App11	CI40
App12	CI40
App13	CI40
App14	CI22
App14	CI40
App14	CI56
App15	CI40
App16	CI22
App16	CI40

[Greg_Deckler]

Sample data that can be copied and pasted would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said...This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376