How to sum a string in matrix as zero value

[gjurcin_cch]

I have one compex DAX where one division needs to happen and get three different kind of results:

1. if the division is equal to 1 the result needs to be "No data in Lake"

2. if the result have zero denominator then result is "No data in BW"

3. otherwise I want to have regular result

Here is the DAX

CALCULATE
(
    IF
    (
        SUMX
        (
            'metadata_data_quality reconcile_status_v1',
            'metadata_data_quality reconcile_status_v1'[kbi_difference]/ABS('metadata_data_quality reconcile_status_v1'[expected_value])
        ) == 1,
        "No data in Lake",
        IF
        (
            ISERROR
            (
                SUMX
                (
                    'metadata_data_quality reconcile_status_v1',
                    'metadata_data_quality reconcile_status_v1'[kbi_difference]/ABS('metadata_data_quality reconcile_status_v1'[expected_value])
                )
            ),
            "No data in BW",
            SUMX
            (
                'metadata_data_quality reconcile_status_v1',
                'metadata_data_quality reconcile_status_v1'[kbi_difference]/ABS('metadata_data_quality reconcile_status_v1'[expected_value])
            )
        )
    ),

    FILTER
    (
        'metadata_data_quality reconcile_status_v1',
        'metadata_data_quality reconcile_status_v1'[process_run_id] == 'metadata_data_quality reconcile_status_v1'[last_process_run_id]
    )
)

The formula is working fine but the issue is coming when the matrix is created. Check the image bellow:

How can I make subtotal to be zero result if I have more then one values in the columns? Like in the case that I have for "No data in BW". Check the image bellow

Or even better how can be strings threated as zero value in subtotals and while collapsing the matrix rows?

Thank you in advance!

[tamerj1]

@gjurcin_cch 

Please try

=
VAR Result =
SUMX (
FILTER (
'metadata_data_quality reconcile_status_v1',
'metadata_data_quality reconcile_status_v1'[process_run_id] = 'metadata_data_quality reconcile_status_v1'[last_process_run_id]
),
'metadata_data_quality reconcile_status_v1'[kbi_difference]
/ ABS ( 'metadata_data_quality reconcile_status_v1'[expected_value] )
)
RETURN
SWITCH (
TRUE (),
ISERROR ( Result ), "No data in BW",
Result = 1, "No data in Lake",
HASONEVALUE ( [YearMonth] ), Result,
"No data in Lake"
)

[gjurcin_cch · Accepted solution]

@tamerj1 I have tried with it seems to be very close but now the filter does not take any actions and not working on the context. Can you explain me the meaning of hasonevalue so I can somehow design the dax on my own?

SWITCH
(
    TRUE (),
    ISERROR ( ratio ), "No data in BW",
    ratio = 1, "No data in Lake",
    HASONEVALUE ('metadata_data_quality reconcile_status_v1'[fiscper]), ratio,
    "No data in Lake"
)

[tamerj1]

@gjurcin_cch 

What do you mean by "the filter does not take any actions and not working on the context"?

HASONEVALUE checks if the column referred to in its argument has only one value in the current filter context. Basically, for the [fiscper] shall have multiple values in the total column which represents an aggregation of multiple months. Can be also replace with ISINSCOPE that shall act the same in this situation. 

[tamerj1]

Hi @gjurcin_cch 

To be honest, I don't fully understand the requirement. However, I may advise to simplify the measure as follows

=
VAR Result =
SUMX (
FILTER (
'metadata_data_quality reconcile_status_v1',
'metadata_data_quality reconcile_status_v1'[process_run_id] = 'metadata_data_quality reconcile_status_v1'[last_process_run_id]
),
'metadata_data_quality reconcile_status_v1'[kbi_difference]
/ ABS ( 'metadata_data_quality reconcile_status_v1'[expected_value] )
)
RETURN
SWITCH (
TRUE (),
ISERROR ( Result ), "No data in BW",
Result = 1, "No data in Lake",
Result
)

[gjurcin_cch]

Hi, @tamerj1 thanks for the optimization of the formula it pretty much better than the previous one. But lets get back to the requirement and try to simplify.

This is what I have now.

data_lake_object_id	expected_kbi_provider	expected_kbi_name	partition_level	process_run_id	2021000	2021020	2022020	Grand Total
cch_bw.mtc.ca_red07_m.v1	CA_Q_D_CA_RED07M_Q07	ktotalfac	0403-RU	20230125_003231_4898235_68697			0	0
			0410-AM	20230125_000338_4037250_19423			0	0
			0415-LV	20230125_001908_2470907_24425			0	0
			0416-EE	20230125_001051_6220341_63416	No data in Lake	No data in Lake	No data in Lake	3
			0417-LT	20230125_001812_9030151_77396		No data in Lake	No data in Lake	2
			0428-BG	20230125_000641_7936029_15792			No data in Lake	No data in Lake
			0430-MK	20230125_002029_2134199_19551			0	0
			0440-RS	20230125_002426_3454789_62214			0	0
			0450-GR	20230125_001155_1327277_95418			0	0
			0470-NG	20230125_002126_8875583_13205			0	0
			0480-GB	20230125_001557_2587576_42395			0	0

But I want to have the result below.

data_lake_object_id	expected_kbi_provider	expected_kbi_name	partition_level	process_run_id	2021000	2021020	2022020	Grand Total
cch_bw.mtc.ca_red07_m.v1	CA_Q_D_CA_RED07M_Q07	ktotalfac	0403-RU	20230125_003231_4898235_68697			0	0
			0410-AM	20230125_000338_4037250_19423			0	0
			0415-LV	20230125_001908_2470907_24425			0	0
			0416-EE	20230125_001051_6220341_63416	No data in Lake	No data in Lake	No data in Lake	0
			0417-LT	20230125_001812_9030151_77396		No data in Lake	No data in Lake	0
			0428-BG	20230125_000641_7936029_15792			No data in Lake	No data in Lake
			0430-MK	20230125_002029_2134199_19551			0	0
			0440-RS	20230125_002426_3454789_62214			0	0
			0450-GR	20230125_001155_1327277_95418			0	0
			0470-NG	20230125_002126_8875583_13205			0	0
			0480-GB	20230125_001557_2587576_42395			0	0