topic Query on DAX Equation in DAX Commands and Tips

Query on DAX Equation

hosea_chumba — Thu, 30 Jun 2022 07:44:58 GMT

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

Re: Query on DAX Equation

SpartaBI — Thu, 30 Jun 2022 09:11:29 GMT

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

Re: Query on DAX Equation

tamerj1 — Thu, 30 Jun 2022 09:31:06 GMT

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

Re: Query on DAX Equation

hosea_chumba — Fri, 01 Jul 2022 07:11:11 GMT

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.

Re: Query on DAX Equation

SpartaBI — Fri, 01 Jul 2022 07:36:24 GMT

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

Re: Query on DAX Equation

hosea_chumba — Fri, 26 Aug 2022 07:37:08 GMT

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