Fetching the list of data of a text column based on the filter criteria on another text column

[rivthebest]

I am looking out for some help in DAX.

Data: https://www.icmagroup.org/membership/List-of-principal-delegates-2/

All the columns are Text columns and they are namely: Company, First Name, Last Name, Member Jurisdiction.

Concatenated First Name and Last Name as Full Name and dropped the columns First Name and Last Name using Power Query Interface.

The final dataset contains: Company, Member Jurisdiction, Full Name. All are text columns. Total 634 rows.

The following DAX I had written in this way:

Count of Member Banks Per Jurisdicton =

CALCULATE(

    COUNT( ICMA_Members[Company] ),

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] )

)

Count of Member Banks Per Jurisdiction and Type =

CALCULATE(

    COUNT( ICMA_Members[Company] ),

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] ),

    FILTER( ICMA_Members, RIGHT( ICMA_Members[Company], 3) = "LLP" )

)

and finally the following DAX:

List of Member Banks Per Jurisdiction and Type =

CALCULATETABLE(

    VALUES( ICMA_Members[Company] ),

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] ),

    FILTER( ICMA_Members, RIGHT( ICMA_Members[Company], 3) = "LLP" )

)

This is the query I am having problem. Instead of count of Member Banks per Jurisdiction per the filter condition I had set, I would like to fetch the list of the Member Banks per Jurisdiction and Filter Condition set. The above DAX formula is syntactically correct but it is not helping me to yield any result as it cannot be used in the Value segment in the Matrix Visual. Here is the error message posted below.

I am quite certain I am making a terrible unpardonable silly mistake and that is why I am seeking your valuable advise. Please help me out.

[rivthebest]

I got the idea... it should be CALCULATE instead of CALCULATETABLE.... thank you...

[rivthebest]

Hi Xiaoxin,

By Following your step I am getting the following error pasted as screenshot:

Please advise.

Regards

Riv

[Ashish_Mathur]

Hi,

Could you share some data to work with and show the expected result.

[rivthebest]

Hi Ashish,

Data: https://www.icmagroup.org/membership/List-of-principal-delegates-2/

All the columns are Text columns and they are namely: Company, First Name, Last Name, Member Jurisdiction.

Concatenated First Name and Last Name as Full Name and dropped the columns First Name and Last Name using Power Query Interface.

The final dataset contains: Company, Member Jurisdiction, Full Name. All are text columns. Total 634 rows.

The following DAX I had written in this way:

Count of Member Banks Per Jurisdicton =

CALCULATE(

    COUNT( ICMA_Members[Company] ),

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] )

)

Count of Member Banks Per Jurisdiction and Type =

CALCULATE(

    COUNT( ICMA_Members[Company] ),

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] ),

    FILTER( ICMA_Members, RIGHT( ICMA_Members[Company], 3) = "LLP" )

)

and Lastly what Xiaoxin has mentioned in his code and I had applied the same with a minor alteration like instead of using CALCULATETABLE I had used CALCULATE and it worked. 

List of Member Banks Per Jurisdiction and Type =

CALCULATE(

    VALUES( ICMA_Members[Company] ),

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] ),

    FILTER( ICMA_Members, RIGHT( ICMA_Members[Company], 3) = "LLP" )

)

Ahish please let me know if any other details I need to furnish

Regards Riv

[rivthebest]

Hi Ashish,

Please disregard my last message

Here is the updated one:

Data: https://www.icmagroup.org/membership/List-of-principal-delegates-2/

All the columns are Text columns and they are namely: Company, First Name, Last Name, Member Jurisdiction.

Concatenated First Name and Last Name as Full Name and dropped the columns First Name and Last Name using Power Query Interface.

The final dataset contains: Company, Member Jurisdiction, Full Name. All are text columns. Total 634 rows.

The following DAX I had written in this way:

Count of Member Banks Per Jurisdicton =

CALCULATE(

    COUNT( ICMA_Members[Company] ),

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] )

)

Count of Member Banks Per Jurisdiction and Type =

CALCULATE(

    COUNT( ICMA_Members[Company] ),

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] ),

    FILTER( ICMA_Members, RIGHT( ICMA_Members[Company], 3) = "LLP" )

)

and Lastly what Xiaoxin has mentioned in his code and I had applied the same with a minor alteration like instead of using CALCULATETABLE I had used CALCULATE and it worked. 

List of Member Banks Per Jurisdiction and Type =

CALCULATE(

    CONCATENATEX(VALUES( ICMA_Members[Company] ), ICMA_Members[Company], ",")

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] ),

    FILTER( ICMA_Members, RIGHT( ICMA_Members[Company], 3) = "LLP" )

)

Ashish please let me know if any other details I need to furnish

Regards Riv

[Ashish_Mathur]

Hi,

Your said that Xiaoxin's formula worked so i assume your question is answered.  If not, then (as requested in my message earlier), share soem data and show the expected result.

[Anonymous]

Hi @rivthebest,

AFAIK, VALUES function will return a list if the current row context has multiple rows. (the returned data type is not supported to use in measure)
I think you need to do concatenate operations with the result to handle if the current row context has been aggregated with multiple rows:

List of Member Banks Per Jurisdiction and Type =
CALCULATETABLE (
    CONCATENATEX ( VALUES ( ICMA_Members[Company] ), [Company], "," ),
    GROUPBY ( ICMA_Members, ICMA_Members[Member Jurisdiction] ),
    FILTER ( ICMA_Members, RIGHT ( ICMA_Members[Company], 3 ) = "LLP" )
)

Regards,

Xiaoxin Sheng

[rivthebest · Accepted solution]

Hi Xiaoxin,

Thank you for your help.. you are a life saviour. A minor change I had to do in your code segment. Instead of using CALCULATETABLE, CALCULATE will be in place and final piece of code is as follows:

List of Member Banks Per Jurisdiction and Type =

CALCULATE(

    CONCATENATEX(VALUES( ICMA_Members[Company] ), ICMA_Members[Company], ",")

    GROUPBY( ICMA_Members, ICMA_Members[Member Jurisdiction] ),

    FILTER( ICMA_Members, RIGHT( ICMA_Members[Company], 3) = "LLP" )

)