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 ChampionPlease 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"
)
- tamerj1
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.