Forum Discussion
How to sum a string in matrix as zero value
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 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" )
5 Replies
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
)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
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"
)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" )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.
