topic Re: Adding further criteria to filter in DAX Commands and Tips

Adding further criteria to filter

gambleave — Mon, 13 Jun 2022 07:15:14 GMT

This seems dead basic, but I keep getting errors with everything I try.

I have a working measure in the form:

=COUNTROWS(FILTER(ClientGroup, [Account Status]=1 && ClientGroup[Tier]="Gold"))

(Account Status is a measure to categorize accounts based on differrent transaction volumes)

I want to expand the filter criteria to include Account Status values of 1, 2 or 3 as well as Tier of Gold or Silver. Can anyone please help?

Re: Adding further criteria to filter

DimaMD — Mon, 13 Jun 2022 07:27:45 GMT

hi gambleave Can you provide a sample of the data and your desired result?

Re: Adding further criteria to filter

gambleave — Mon, 13 Jun 2022 07:51:51 GMT

Afraid not, as it's in a corporate environment and I need to use my phone just to access this forum!

I thought this should be a measure selection/Syntax problem since it can be approached by creating additional measures for different permutations of Account Status and Tier, then adding them together.

Re: Adding further criteria to filter

DimaMD — Mon, 13 Jun 2022 08:06:15 GMT

gambleave Try it

CALCULATE( COUNTROWS('ClientGroup'), FILTER(ClientGroup, [AccountStatus] in {"1","2","3"} && ClientGroup[Tier] in { "Gold", "Silver"}) )

Re: Adding further criteria to filter

gambleave — Mon, 13 Jun 2022 08:39:57 GMT

Thanks for the suggestion. For some reason, I end up with the error "MdxScript(Model) (257,105) Calculation error in measure ...Function 'CONTAINSROW' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

Re: Adding further criteria to filter

wdx223_Daniel — Mon, 13 Jun 2022 08:54:57 GMT

[AccountStatus] in {1,2,3}

Re: Adding further criteria to filter

gambleave — Mon, 13 Jun 2022 09:16:20 GMT

Thanks a million to you both for the help. This works perfectly.

Oddly, I tried the same measure in a Power Pivot version of my project with Excel 2016, but it doesn't work there (...The syntax for 'in' is incorrect...). Is it that this DAX operator doesnt work with Excel?

Re: Adding further criteria to filter

DimaMD — Mon, 13 Jun 2022 10:05:25 GMT

gambleave Check the data format, numeric or text