Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am looking to create a measure (as I am querying the db and not importing) for the campaign member status. An example data set is pasted below:
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 |
I want to see a % of click to open ratio from this table for each of the campaign. For example, for camapign A1, I have 3 opens and 2 clicks. My measure should return 67%. Similarly for campaign A2, there are 4 opens and 2 clicks, so it should return 50% click to open ratio.
Probably this is a basic question, but I get stuck with DAX all the time. Thanks for all your help 🙂
Solved! Go to Solution.
@sujatakaran , Try like
a measure like
divide(calculate(count(Table[Campaign]), filter(Table, Table[status] ="Clicked")),calculate(count(Table[Campaign]), filter(Table, Table[status] in {"Open"})))
Hi @sujatakaran
By your description, Your measure may be missing ")" after count().
Here is the correct one :
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" } )
))
Also, you could create a measure as follows:
CTR2 =
COUNTROWS(FILTER('Table',[Status]="Clicked"))
/COUNTROWS(FILTER('Table',[Status] in {"Open","1-Opened","2-Open"}))
The final output is shown below:
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.
Hi @sujatakaran
By your description, Your measure may be missing ")" after count().
Here is the correct one :
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" } )
))
Also, you could create a measure as follows:
CTR2 =
COUNTROWS(FILTER('Table',[Status]="Clicked"))
/COUNTROWS(FILTER('Table',[Status] in {"Open","1-Opened","2-Open"}))
The final output is shown below:
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.
Hi,
Please help on this topic, I am stuck here 😞
Best
Sujata
Hi @amitchandak ,
Thanks for quick response. I thought this would work, but the member status in the actual data set has multiple entrees for the similar status. Here is the screenshot of the formula that I tried:
I tried it with || and && as well, but it still throws same error.
Best
Sujata
@sujatakaran , Try like
a measure like
divide(calculate(count(Table[Campaign]), filter(Table, Table[status] ="Clicked")),calculate(count(Table[Campaign]), filter(Table, Table[status] in {"Open"})))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |