Forum Discussion

klopdaddy's avatar
klopdaddy
Regular Visitor
4 years ago
Solved

Virtual Table Row Count with Boolean Expression

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

  • klopdaddy's avatar
    klopdaddy
    4 years ago

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

    Thanks!

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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". 

    • klopdaddy's avatar
      klopdaddy
      Regular Visitor
      4 years ago

      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

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        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.