Calculated field is showing blank for related table

[sujatakaran]

Hello,

I have 2 tables, 
1st Table, campaign member table:

Campaign Id	Member Id	Status
A1	B1	Member
A1	B1	Open
A1	B1	Clicked
A1	B2	Member
A1	B2	Open
A1	B3	Member
A1	B3	Open
A1	B3	Clicked
A1	B4	Member
A2	C1	Member
A2	C1	Open
A2	C1	Clicked
A2	C2	Member
A2	C2	Open
A2	C3	Member
A2	C3	Open
A2	C3	Clicked
A2	C4	Member
A3	C1	Member
A3	C1	Open
A3	C1	Clicked
A3	C2	Member
A3	C3	Member
A3	C3	Open
A3	C3	Clicked
A3	C4	Member
A4	D1	Member
A4	D1	Open
A4	D1	Clicked
A4	D2	Member
A4	D2	Open
A4	D3	Member
A4	D3	Open
A4	D3	Clicked
A4	D4	Member

2nd Table : Campaign table

Campaign Id	Campaign Name
A1	Camp 1
A2	Camp 2
A3	Camp 3
A4	Camp 4

I have a click through measure in the campaign member table which goes like this:

CTR1 =
DIVIDE (
CALCULATE (
COUNT ( 'Table'[Campaign Id] ),
FILTER ( 'Table', [Status] = "Clicked" )
),
CALCULATE (
COUNT ( 'Table'[Campaign Id] ),
FILTER ( 'Table', [Status] IN { "Open", "1-Opened", "2-Open" } )
))

The calculation is working just fine now but I ran into an issue with this. The two tables are related by campaign id with many to one relationship.

Now, I am using the Campaign name from campaign table to filter the data and trying to find what is the Click through ratio for each campaign. This shows me blank in table or card visulations, the other calculation works just fine. For example, if I am taking count of 'Opens', then the table shows the actual value, same goes for 'Clicks' but when I divide these two and try to get the CTR for the campaigns, it just goes blank. 
I did try changing the filter direction to both , but that does not solve the issue. I am wondering if someone has come across such situation or any other help here. 

[v-jayw-msft]

Hi @sujatakaran ,

You may try to inactive the relationship and use the below formula instead.

CTR1 = DIVIDE(
    CALCULATE(COUNT('Campaign Member'[Campaign Id]),
    FILTER(ALL('Campaign Member'),'Campaign Member'[Status] IN {"3-Clicked","clicked", "1-Clicked","2-Clicked"}&&'Campaign Member'[Campaign Id] in VALUES(Campaign[Campaign Id]))
    ),
    CALCULATE(
        COUNT('Campaign Member'[Campaign Id]),
        FILTER(ALL('Campaign Member'),'Campaign Member'[Status] IN {"3-Open","2-Open","1-Open","Open"}&&'Campaign Member'[Campaign Id] in VALUES(Campaign[Campaign Id]))
    ))

Best Regards,

Jay

[HotChilli]

There's no problem in the dummy pbix (as you note yourself) so I can't help you with that.

I suggest you investigate the relationships and data quality.

[HotChilli]

Instead of me guessing, can you link your pbix (from a 3rd party site) and I'll have a look.

It'll be sorted in about 2 minutes

[HotChilli]

Thanks for the data.

I'm not really following this part "This shows me blank in table or card visulations, the other calculation works just fine"

Can you show us how you are filtering please (slicer or filter pane?) and what's in the visual that isn't giving a correct answer?

[sujatakaran]

Okay, I have created a dummy pbix and here is the link for the same.

[sujatakaran · Accepted solution]

Thanks for the reply.
I made 3 measures 
1. Just counting the number of opens

2. Number of Clicks

3. Clicks divided by Opens, that is to calculate Click through ratios for each campaign (CTR)
Now, I am filtering the CTR by campaign name(drop down) to get the CTR for each campaign. I am able to get the first two numbers, but the 3rd one shows blank.
Hope I am able to clarify your questions.