DAX calculation in one table that looks for the availability of a value in second table

[pprasad2]

Hi All,

I need help.

I need to write a DAX calculation that gives me the count of all 'ExceptionNames' from 'Main Table' only when the exception present in second custom table 'Eligible Exceptions'? Could you help?

Updated Entries(Current) = CALCULATE(COUNT(MAIN_TABLE[ExceptionName]), FILTER('MAIN_TABLE', 'MAIN_TABLE[ExceptionName] = RELATED('Eligible_Exceptions'(ExceptionName))

Basically I have list of 40+ reason codes in my table but I need to consider 6 of them now. so i created a custom table with those 6 reasoncodes and trying the search in the custom table before counting in the main table.

[V-lianl-msft · Accepted solution]

Hi @pprasad2 ,

Based on your description, I created a sample file. You can use FIND or SEARCH for string comparison, and then count the column.

Column = 

  IF(
      SUMX('Table',
           FIND(
                'Table'[Product],
                Sheet2[Product]
                ,,0
               )
          ) > 0,
      CALCULATE(COUNT(Sheet2[Country])
            ,ALLEXCEPT(Sheet2,Sheet2[Product]
     )))

Sample .pbix

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

[pprasad2]

The one I used did not work. It errored out. so I am not sure the way I was doing right or not. I just need a mechanism/logic to do count count in main table for only those records that are have a same value in my exception table.

[Pragati11]

HI @pprasad2 ,

Can you elaborate more please? Is your DAX (which is specified in your query) giving you some error? Or there is some other issue you are facing?

Thanks,

Pragati