Distinctcount with filter on a different column

[ridhwaanmayet]

Enagagement Rate SO = COUNTX(SUMMARIZE('Social Overview - Created by Owner et al', 'Social Overview - Created by Owner et al'[Headline]), FILTER(SUMMARIZE('Social Overview - Created by Owner et al', 'Social Overview - Created by Owner et al'[Headline]), [Type] = “Interaction”)/COUNTX(FILTER('Social Overview - Created by Owner et al','Social Overview - Created by Owner et al'[Type]="Post")))

I am trying to do the following:

Distinctcount of [Heading] where I have filtered the table for [Type] = "Interaction"

The problem is that distint count only works on a column and my filter is on a different column.

The second part is that I want to divide this number by the Count where [Type]="Post"

I am very new to PowerBI and equally green with Excel, any suggestions?

The error I get is "The following syntax error occurred during parsing: Invalid token, Line 1, Offset 242, “.

[v-ljerr-msft]

Hi @ridhwaanmayet,

Could you post your table structures(including table relationships) with some sample data and your expected result, so that we can better assist on this issue? 

Regards

[ridhwaanmayet]

Hi @v-ljerr-msft,

There aren't any relationships. The measure is based on a single table 'Social Overview ...'.

The measure will calculate engagement i.e. the number of conversations (posts which were interacted with) by the brand divided by the number of posts by the brand.

To get conversations I want to calculate the number of distinct interactions. To do this I try the following:

Enagagement Rate SO = 
CALCULATE(
  DISTINCTCOUNT('Social Overview - Created by Owner et al'[Headline]),
  FILTER('Social Overview - Created by Owner et al', 'Social Overview - Created by Owner et al'[Type] = “Interaction”)

This is where I get the error: 
The following syntax error occurred during parsing: Invalid token, Line 1, Offset 182, “.

The second half of the measure is:

/ COUNTX(
  FILTER('Social Overview - Created by Owner et al',
    'Social Overview - Created by Owner et al'[Type]="Post"),
  'Social Overview - Created by Owner et al'[Type])

The end result should be a percentage which I will put into a gauge. The measure is important as it is used in other measures.

Regards and Thanks!

[ridhwaanmayet]

The other way I have tried it is:

Enagagement Rate SO = CALCULATE(CALCULATE(filter('Social Overview - Created by Owner et al', 'Social Overview - Created by Owner et al'[Type] = “Interaction”),DISTINCTCOUNT('Social Overview - Created by Owner et al'[Headline])) / COUNTX(FILTER('Social Overview - Created by Owner et al','Social Overview - Created by Owner et al'[Type]="Post")))