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...
DAX Equation
  • SpartaBI's avatar
    SpartaBI
    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

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi hosea_chumba 
Here is a sample file with the solution for both calculated column and measure options https://we.tl/t-tw6wKBDwJt

Falg Column = 
VAR CurrentClientTable =
    CALCULATETABLE ( Loans, ALLEXCEPT ( Loans, Loans[Client ID] ) )
VAR CurrntLoan =
    Loans[Loan Product ID]
RETURN
    IF (
        COUNTROWS ( CurrentClientTable ) = 1 
            && CurrntLoan = "Education",
            1
    )
Falg Measure = 
SUMX (
    VALUES ( Loans[Client ID] ),
    IF ( 
        COUNTROWS ( CALCULATETABLE ( Loans, ALLEXCEPT ( Loans, Loans[Client ID] ) ) ) = 1 
            && CALCULATE ( SELECTEDVALUE ( Loans[Loan Product ID] ) ) = "Education",
            1,
            0
    )
)