Countrows function for 2 colums

[EricKautz]

Good afternoon,

I am trying to create a measure to count the number of rows that meet 2 different column criteria and the dax equation I'm using is not pulling in the correct total. The count is supposed to meet criteria from 2 columns. First, if a value is greater than 10,000 and the number of days is = 1. I am trying to put this data into a matrix table. The first column has the total number of each Asset Class over 10k, that data is correct. When I add the 2nd measure to the matrix table, its not splitting up the values equal 1 between FX and InterestRate, but giving me the combined total.

This is the formula I used:

MTM Greater than 10,000 and 1 = COUNTROWS(

    FILTER(

        ALL('cftc 10 %'),

        'cftc 10 %'[ABS MTM Difference Value] > 10000 && 'cftc 10 %'[age] = "1"))

Chart Data:

Asset Class	ABS MTM Difference Value	Age
FX	$25,421.00	1
InterestRate	$11,797.00	2
InterestRate	$5,256.00	4

[sevenhills]

Try with ALLSELECTED in place of ALL and see if it works!

[EricKautz]

No, it did not work.

[muhammad_786_1 · Accepted solution]

I hope it'll work for you.

MTM Greater than 10,000 and 1 =
CALCULATE(
COUNTROWS('cftc 10 %'),
'cftc 10 %'[ABSMTMDifferenceValue] > 10000,
'cftc 10 %'[age] = "1"
)

This approach correctly counts the rows per AssetClass that meet your both criteria.

My suggestion: It's better to avoid spaces in table and column names to keep your DAX formulas cleaner and more readable.

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

Follow on LinkedIn

[EricKautz]

This worked for me, thank you.