Forum Discussion

hosea_chumba's avatar
hosea_chumba
Helper I
4 years ago
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...
  • SpartaBI's avatar
    4 years ago

    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

     

     





          

    Showcase Report – Contoso By SpartaBI