Forum Discussion
Solved
Query on DAX Equation
Kindly assist with the below; Consider the table below: Client ID Loan Product ID 1 Education 2 Crop 3 Education 4 Animal 1 Crop 1 Water 4 Education I would like...
hosea_chumba create this calculated column:
Status = VAR _current_client = 'Table'[Client ID] VAR _client_products = CONCATENATEX(FILTER('Table', 'Table'[Client ID] = _current_client), 'Table'[Loan Product ID], ", ") VAR _client_products_without_education = CONCATENATEX(FILTER('Table', 'Table'[Client ID] = _current_client && 'Table'[Loan Product ID] <> "Education"), 'Table'[Loan Product ID], ", ") VAR _has_education = CONTAINSSTRINGEXACT(_client_products, "Education") VAR _has_anything_else = NOT( ISBLANK(_client_products_without_education)) VAR _result = SWITCH( TRUE(), _has_education && _has_anything_else, "Education + more", _has_education && NOT(_has_anything_else), "Only Education", "No Education" ) RETURN _result
hosea_chumba create this calculated column:
Status =
VAR _current_client = 'Table'[Client ID]
VAR _client_products =
CONCATENATEX(FILTER('Table', 'Table'[Client ID] = _current_client), 'Table'[Loan Product ID], ", ")
VAR _client_products_without_education =
CONCATENATEX(FILTER('Table', 'Table'[Client ID] = _current_client && 'Table'[Loan Product ID] <> "Education"), 'Table'[Loan Product ID], ", ")
VAR _has_education = CONTAINSSTRINGEXACT(_client_products, "Education")
VAR _has_anything_else = NOT( ISBLANK(_client_products_without_education))
VAR _result =
SWITCH(
TRUE(),
_has_education && _has_anything_else, "Education + more",
_has_education && NOT(_has_anything_else), "Only Education",
"No Education"
)
RETURN
_result
Hi Sparta,
Why did you use Concatenatex and filter i.e. "concatenatex(filter", as in what is the logic behind it. I know concatenatex returns combined items in each row however i need to understand what it does when you add a filter.
hosea_chumba it does exactly the same, I just gave him a filtered table to work on so it will have only the relevant values to concatenate
Is it possible to use Calculate in place of Concatenatex and arrive at the same solution?