Query on DAX Equation

[hosea_chumba]

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

[tamerj1]

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

[SpartaBI · Accepted solution]

@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]

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]

@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

[hosea_chumba]

Is it possible to use Calculate in place of Concatenatex and arrive at the same solution?