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