cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculated field is showing blank for related table

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.

1 ACCEPTED SOLUTION
Helper I

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.

6 REPLIES 6
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

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.

Super User

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

Super User

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?

Helper I

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

Helper I

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors