Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sujatakaran
Helper I
Helper I

Calculated field is showing blank for related table

Hello,

 

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

Campaign IdMember IdStatus
A1B1Member
A1B1Open
A1B1Clicked
A1B2Member
A1B2Open
A1B3Member
A1B3Open
A1B3Clicked
A1B4Member
A2C1Member
A2C1Open
A2C1Clicked
A2C2Member
A2C2Open
A2C3Member
A2C3Open
A2C3Clicked
A2C4Member
A3C1Member
A3C1Open
A3C1Clicked
A3C2Member
A3C3Member
A3C3Open
A3C3Clicked
A3C4Member
A4D1Member
A4D1Open
A4D1Clicked
A4D2Member
A4D2Open
A4D3Member
A4D3Open
A4D3Clicked
A4D4Member


2nd Table : Campaign table

Campaign IdCampaign Name
A1Camp 1
A2Camp 2
A3Camp 3
A4Camp 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

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. 

View solution in original post

6 REPLIES 6
v-jayw-msft
Community Support
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.
HotChilli
Super User
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.

HotChilli
Super User
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

HotChilli
Super User
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?

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

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. 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors