topic Re: Help with counting items that are common to 2 different measures in DAX Commands and Tips

Help with counting items that are common to 2 different measures

Learning_PBI202 — Mon, 10 Nov 2025 16:51:16 GMT

I have measures in 2 separate tables (Table A = Pre_Check_table and Table B = Order_check_table). The measures give a count by group of the number of orders in each table and the count of each number of orders should match between Table A and Table B. I need to have a count of anytime the measure in Table A is blank and also the total count of unique orders between Tables A and B. I tried making a measure in Table A that was if(isblank(Table A[measure]), 1, 0) but the summing on that measure isn't calculating correctly.

Re: Help with counting items that are common to 2 different measures

Smitho — Mon, 10 Nov 2025 16:56:43 GMT

Could you give us a maple of the data. And the measure you are using.

Re: Help with counting items that are common to 2 different measures

Learning_PBI202 — Mon, 10 Nov 2025 17:40:03 GMT

My measure for Table A is:

Count_check = calculate(countrows(Table_A)), Table_A[Group] = "Group_1")

My measure for Table B is

Count_Check = calculate(countrows(Table_B), Table_B[Group] = Group_1 && Table_B[Order] = 'Y" ||

Table_B[Group] = Group_2 && Table_B[Order] = 'Y" && Table_B[Done_for_Group_1] = 1)

Re: Help with counting items that are common to 2 different measures

Zanqueta — Mon, 10 Nov 2025 18:45:26 GMT

Hi Learning_PBI202,

I'm not sure about your issue, but, let me try:

Your issue with counting BLANKs stems from ISBLANK being evaluated at a summary level (Total) instead of the row granularity. The second requirement needs the combination of distinct counts from two separate tables.

It is crucial to ensure your original measures are optimised. I have simplified your Count_check_B using variables for better readability and performance.

Count_check_A = CALCULATE( COUNTROWS('Table_A'), 'Table_A'[Group] = "Group_1" )

Count_check_B = VAR Filter_Condition = 'Table_B'[Order] = "Y" VAR Filter_Groups = ( 'Table_B'[Group] = "Group_1" ) || ( 'Table_B'[Group] = "Group_2" && 'Table_B'[Done_for_Group_1] = 1 ) RETURN CALCULATE( COUNTROWS('Table_B'), Filter_Condition, Filter_Groups )

Use SUMX to iterate over the filter context (e.g., Order_ID) and check ISBLANK against the revised measure [Count_check_A].

Count_BLANKs_Table_A = SUMX( -- Iterate over the granularity field (e.g., Order_ID) ALLSELECTED('Table_A'[Order_ID]), VAR Current_Count = [Count_check_A] RETURN IF( ISBLANK(Current_Count), 1, -- Count 1 if the measure is BLANK for this context 0 ) )

Create a virtual table (variable) that combines the ID columns from both tables for a single distinct count.

Total_Unique_Orders_A_B = VAR Temp_Union_Orders = UNION( SELECTCOLUMNS(ALL('Table_A'), "Order_ID", 'Table_A'[Order_ID]), SELECTCOLUMNS(ALL('Table_B'), "Order_ID", 'Table_B'[Order_ID]) ) RETURN DISTINCTCOUNT(Temp_Union_Orders[Order_ID])

If this answer solves your problem, please mark it as correct to help other community members.

Re: Help with counting items that are common to 2 different measures

ValtteriN — Tue, 11 Nov 2025 09:08:23 GMT

Hello,

Based on your screenshot the group column contains only numbers. So instead of Table_A[Group] = "Group_1" use Table_A[Group] = "1". This is also the case for your second measure. Additionally one easy way to troubleshoot these kind of table function based measures is to create a calculated table with the same filter conditions and see how the data looks.

So here you could use FILTER(Table_A, Table_A[Group] = "1") and see if the data matches your assumptions. This is a good approach if the data amounts you are working with are relatively small.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Help with counting items that are common to 2 different measures

v-dineshya — Thu, 13 Nov 2025 05:42:54 GMT

Hi Learning_PBI202 ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below steps.

1. Created sample data based on your screenshot.

2. Create below measures.

Common_Items =

COUNTROWS(

INTERSECT(

SELECTCOLUMNS( 'Table A', "Index", 'Table A'[Index] ),

SELECTCOLUMNS( 'Table B', "Index", 'Table B'[Index] )

)

Distinct_Orders_All =

COUNTROWS(

UNION(

SELECTCOLUMNS( 'Table A', "Index", 'Table A'[Index] ),

SELECTCOLUMNS( 'Table B', "Index", 'Table B'[Index] )

)

Missing_in_A =

COUNTROWS(

EXCEPT(

SELECTCOLUMNS( 'Table B', "Index", 'Table B'[Index] ),

SELECTCOLUMNS( 'Table A', "Index", 'Table A'[Index] )

)

MissingA_Flag =

IF(

ISBLANK(

LOOKUPVALUE( 'Table A'[Index], 'Table A'[Index], SELECTEDVALUE( 'Table B'[Index] ) )

)

Please refer below output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

Re: Help with counting items that are common to 2 different measures

v-dineshya — Mon, 17 Nov 2025 04:00:54 GMT

Hi Learning_PBI202 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

Re: Help with counting items that are common to 2 different measures

Learning_PBI202 — Mon, 17 Nov 2025 17:32:48 GMT

It still appears like the measures aren't accurately counting when it doesn't calculate a value for the order, it correctly identifies the ones that have both but does not accurately count the total number of orders combined.

Re: Help with counting items that are common to 2 different measures

v-dineshya — Tue, 18 Nov 2025 06:26:06 GMT

Hi Learning_PBI202 ,

Thank you for the update. Please refer below updated measures.

Common_Items =

VAR Result =

COUNTROWS (

INTERSECT (

SELECTCOLUMNS ( 'Table A', "Index", 'Table A'[Index] ),

SELECTCOLUMNS ( 'Table B', "Index", 'Table B'[Index] )

)

RETURN COALESCE ( Result, 0 )

2. In your Table A and Table B probably have relationships, If there is a relationship between Table A[Index] and Table B[Index], then the measure receives filters from the visuals or model. This will shrink the UNION.

Please refer below measure.

Distinct_Orders_All =
CALCULATE (
COUNTROWS (
UNION (
SELECTCOLUMNS ( 'Table A', "Index", 'Table A'[Index] ),
SELECTCOLUMNS ( 'Table B', "Index", 'Table B'[Index] )
)
),
REMOVEFILTERS('Table A'),
REMOVEFILTERS('Table B')
)

Please refer below output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries. If the issue is still persist, Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided. If possible please provide sample pbix file. It will help us to resolve the issue

Regards,

Dinesh

Re: Help with counting items that are common to 2 different measures

v-dineshya — Fri, 21 Nov 2025 07:48:58 GMT

Hi Learning_PBI202 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh