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 IDLoan Product ID
1Education
2Crop
3Education
4Animal
1Crop
1Water
4Education

I would like to identify client IDs that got "Education" loan product ID but did not get any other loan product ID, and also to identify client IDs that got "Education" loan product ID plus any other loan product ID.

Please help Nested Filter DAX Query 

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

5 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    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

    • hosea_chumba's avatar
      hosea_chumba
      Helper I
      4 years ago

      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.

      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        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

  • 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
    )
)