topic Re: Virtual Table Row Count with Boolean Expression in DAX Commands and Tips

Virtual Table Row Count with Boolean Expression

klopdaddy — Fri, 08 Apr 2022 19:55:23 GMT

Hello-

I am tryint to count the # of events by comparing two calculated measures and returning the count of only those rows that meet the condition. I've been trying via the DAX statement and creation of a virtual tabkle from a much larger SQL table. The below expression appears to ignore the results of the comparision and return all rows of the virtual table.

When i substitute the COUNTAX operator with a SUMX on the same calculated column (w/o the comparison) the statement returns the correct value. Any suggestions would be greatly appreciated.

Event_Over_Budget_Count = CALCULATE(
COUNTAX(
SUMMARIZE(
table_A, table_A[eventName],
"Spend Ratio", DIVIDE
CALCULATE(
SUM (table_A[eventspend]), DATESINPERIOD(table_A[date], MAX(table_A[date]), -1, day)),
DIVIDE( MAX( table_A[budgetamt]), DATEDIFF(MIN(table_A[eventStartDate]), MAX(table_A[event[EndDate]), DAY) + 1))),
([Spend Ratio] >1 )), lookup_Table[event_active_flag] = "Y")

Re: Virtual Table Row Count with Boolean Expression

tamerj1 — Fri, 08 Apr 2022 20:23:42 GMT

klopdaddy

COUNTX won't work with boolean condition. Boolean condition will return either one or zero and CONTX will count booth.
As you've already mentioned in your post, SUMX will provide perfecr count of "1".

Re: Virtual Table Row Count with Boolean Expression

klopdaddy — Fri, 08 Apr 2022 20:41:00 GMT

Thanks for the quick response. Do you have any suggestions on how to count the number of rows of the virtual table that meet the conditions specified? I don't want the sum of the values from the expression i want to be able to compare each value to a criteria (in this case '1') and return a count of only those rows that meet the requirement. Do you know of a better strategy?

Thanks,
Sean

Re: Virtual Table Row Count with Boolean Expression

tamerj1 — Fri, 08 Apr 2022 20:50:41 GMT

klopdaddy

Just replace CONTX with SUMX to cout the trure condition. Boolean condition can be summed. The sum would be the count of true cases as true is translated to 1.

Re: Virtual Table Row Count with Boolean Expression

klopdaddy — Fri, 08 Apr 2022 21:53:24 GMT

That worked with the help of an if statement in my virtual column to create the boolean output to sum.

Thanks!

Re: Virtual Table Row Count with Boolean Expression

tamerj1 — Fri, 08 Apr 2022 22:08:57 GMT

klopdaddy

If you find my reply helpful you may consider selecting it as acceptable solution. Thank you and have a great weekend!