Help with counting items that are common to 2 different measures

[Learning_PBI202]

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.

[Zanqueta]

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.

[Learning_PBI202]

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)

Table ATable B

[v-dineshya]

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

    ),

    1,

    0

)

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

[Learning_PBI202]

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.

[v-dineshya]

Hi @Learning_PBI202 ,

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

1.

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

[v-dineshya]

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

[v-dineshya]

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

[ValtteriN]

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/

[Smitho]

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