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" )
Community ChampionHi 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 |