cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
raymond Member
Member

Measure with Variables and Filters

Hi,

I have simpliefied my example. I want to calculate the number of E-Mail Opens with a filter for "Welcome-Phase". Of course there are other phases as well such as "Follow Up". It works fine though BUT when I display it in a matrix or table with a the column "Phase", every row contains the same value. Whereas there should be only a value for "Welcome-Phase" and e.g. a 0 for "Follow Up". 

 

Does anyone have solution how to get to the correct value?

 

SCORE = 
VAR score =
    CALCULATE(
        [mailing-opens] ;
        [Phase] IN {"Welcome-Phase"} ;
        [Art] IN {"Initial"}
        )

RETURN
score
6 REPLIES 6
Highlighted
dedelman_clng New Contributor
New Contributor

Re: Measure with Variables and Filters

By putting the column [Phase] in a matrix/table, you are adding the filters implicitly in the visualization and don't need them in the measure.

 

So your code should be

 

SCORE = 
VAR score =
    CALCULATE(
        [mailing-opens] ;
        [Art] IN {"Initial"}
        )

RETURN
score

(if you are putting [Art] in the visual as well, you can remove the measure altogether as you would just use [mailing-opens] as the value

 

Hope this helps

David

raymond Member
Member

Re: Measure with Variables and Filters

@dedelman_clng Thank you.

 

However, what if the following is the case Smiley Happy

 

SCORE = 

VAR scoreA =
    CALCULATE(
        [mailing-opens] ;
        [Phase] IN {"Welcome-Phase"} ;
        [Art] IN {"Initial"}
        )

VAR scoreB
    CALCULATE(
        [mailing-klicks] ;
        [Phase] IN {"Welcome-Phase"} ;
        [Art] IN {"Result"}
        )

RETURN
scoreA + scoreB

 

What I hope to see in a matrix table is the Result of scoreA + scoreB for the "Welcome-Phase" and in a drill down to "Art" the separate values for "Result" and "Initial"

dedelman_clng New Contributor
New Contributor

Re: Measure with Variables and Filters

Can you provide a sample of data and your expected results?

raymond Member
Member

Re: Measure with Variables and Filters

Sure. I have uploaded a sample to dropbox. click here

 

It is a quite simple case. There E-Mailings and Phases. I want to calculate a score for E-Mail A and E-Mail B. The scores are different. Hence on the level of the Initial Phase the value is correct. In the drill down the values are not. Furthermore under Results the same value is shown. This should be 0. 

2019-01-18 15_10_49-Sample - Power BI Desktop.png

 

For A and B and the score on the initial level the values should be as such:

2019-01-18 15_13_07-Sample - Power BI Desktop.png

dedelman_clng New Contributor
New Contributor

Re: Measure with Variables and Filters

Hi @raymond

 

In the very specific instance you provided, this code gets you what you want, but I doubt it is very scalable since there are still hard-coded values inside.

 

New Score = 
var __Email = SELECTEDVALUE(Mailings[Name])
var __Phase = SELECTEDVALUE(Mailings[Phase])
var __A =     CALCULATE(
        DIVIDE( [click-rate] , [click-rate TARGET 20%] , BLANK() ) ,
        Mailings[Phase] IN {"Initial"} ,
        Mailings[Name] IN {"E-Mail_A"})

var __B =     CALCULATE(
        DIVIDE( [click-rate] , [click-rate TARGET 75%] , BLANK() ) ,
        Mailings[Phase] IN {"Initial"} ,
        Mailings[Name] IN {"E-Mail_B"}
        )

return

IF(
   ISBLANK(__Email), //SELECTEDVALUE returns BLANK if more than one value comes back
   __A+__B, 
    SWITCH(
        TRUE(), 
        __Email = "E-Mail_A", __A,
        __Email = "E-Mail_B", __B
    ))

It's also worth noting that the sample report file you provided, and the 2nd set of detail do not really match the initial question. I realize that you were trying to obscure sensitive data, but if you look at your 2nd and 3rd posts, they're quite different.

 

Sorry I couldn't be of more help

David

raymond Member
Member

Re: Measure with Variables and Filters

Hi back, you are right, the data is different due to sensitive data. I had to solve the issue differently - in this case I was just using calculated columns and another table. 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 34 members 983 guests
Please welcome our newest community members: