Grouping a Measure by a field in a related table

[Buatti]

Hi all, first time post!

I'm trying to create a visual that shows the results of a measure of a field in Table A, grouped by a field in a related table (Table B). 

The Measure I have is:

# Premium Customers =
CALCULATE (
COUNT ( 'Table A'[Customer ID] ),
'Table A'[Customer Class] = "Premium"
)

I want to create a visual that shows the number of Premium Customers (in Table A) by Magazine Subscription (in Table B) but come up with this:

I have discovered that if I change the relationship between the tables so that Cross filter direction is Both, I can get the desired result (below). However I have read that this is not best practice - is there a reasonable alternative?

[v-yalanwu-msft · Accepted solution]

Hi, @Buatti ;

Try it.

# Premium Customers = 
IF (
    ISINSCOPE ( TableB[Magazine Sub] ),
    CALCULATE (
        COUNT ( 'TableB'[Customer ID] ),
        'Table A'[Customer Class] = "Premium"
    ),
    CALCULATE (
        COUNT ( 'Table A'[Customer ID] ),
        'Table A'[Customer Class] = "Premium"
    )
)

The final show:

You need to use 'TableB'[Customer ID] , You can use my measure and establish a two-way relationship to see if the result is the same.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Buatti]

Thanks for your suggestion however this doesn't address my original problem. I want to count the number of records in Table A and group by the Magazine Subscription in Table B. That way I can also see the number of customers who don't have a magazine subscription. I am unsure why this has been marked as a solution.

[Ashish_Mathur]

Hi,

Let the relationship remain as Many to One and Single.  In Table B, write a calculated column formula to bring over the Customer Class from Table A.  The calculated column formula should be C_Class = related('TableA'[Customer Class]).  To your visual, drag the Magazine subscription from Table B and write this measure

Measure = calculate(distinctcount('Table B'[Customer ID]),'Table B'[C_Class] ="Premium")

Hope this helps.

[Buatti]

Thanks Ashish, however this counts the Customer IDs in Table B (Magazine Subscriptions). I want to count the number of Customers (Table A) regardless if they have a subscription or not. Is it possible to count the records in Table A and group by a field in Table B?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Buatti]

Thanks for the suggestion, however the output is the same. Is there another way to replicate the cross filtering without changing the relationship?

[amitchandak]

@Buatti , Try like

# Premium Customers =
CALCULATE (
COUNT ( 'Table A'[Customer ID] ),
filter('Table A', 'Table A'[Customer Class] = "Premium")
)

refer

http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/