cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aguiardavid Frequent Visitor
Frequent Visitor

Re: Calculated column based on measurements.

Highlighted
richbenmintz
Advisor

Re: Calculated column based on measurements.

Hi @aguiardavid,

 

Have a look at the following, basically check for empty and replace with 0, this will ensure that your column will behave as you expect.

 

Legend = 
var qtd_pot = if(ISBLANK(CALCULATE(sum(Table2[qtd_cli_potential]))), 0,CALCULATE(sum(Table2[qtd_cli_potential]))) 
var qtd_pot_vend = if(ISBLANK(CALCULATE(sum(Table2[qtd_cli_pot_vend]))), 0,CALCULATE(sum(Table2[qtd_cli_pot_vend])))
return 
if(qtd_pot>0 && qtd_pot_vend>0,
"Ok", "Warning")

 

aguiardavid Frequent Visitor
Frequent Visitor

Re: Calculated column based on measurements.

hi @richbenmintz

 

When you filter one of the facts (Sales2), it does not recalculate the field.

See the example of the "Result" field:
If "Sales2"> 50 then "OK", otherwise "warning".

I did what I suggested (I only changed the greater than 0 to greater than 50 in sales2)

If filter color = green, in the Sales2 table, the value becomes 20. That is, less than 50. It should be Warning, but it is OK.

In the attached file, the correct function would be:
- Marking on the "Black" and "Green" filter, TV Ok, Radio Warning.
- Marking on the filter only "Green", both Warning.

 

File

https://grupopetropolis-my.sharepoint.com/:u:/g/personal/00203459_grupopetropolis_com_br/EQKTza-EPxZ...

 

 

richbenmintz
Advisor

Re: Calculated column based on measurements.

Hi @aguiardavid,

 

You have a couple of Issues:

1. Your Sales 2 does not have an active relationship to the Sales 1 Table, both seem to be fact tables related to Product. there is no way that the color you choose from Sales 2 will filter sales 1 column without a measure that specifically using that inactive relationship, even so it is related to the sales amount and does not really make sense to me.

2. Calculated Columns only refresh their value when the model is refresh, slicers and filtering have no effect on them, so even if the relationship between Sales 1 and Sales 2 were active and made sense the filtering would not change the behaviour of the Calc Column.

 

I have updated your Pbix file, added a color dimension, moved the sales 2 column into sales 1 fact table, added a measure that shows the result as this will get evaluated when filters and slicers are used

 

https://1drv.ms/u/s!AhCeuF2piSWMgcBSvkvZ8VPegI2cDg

aguiardavid Frequent Visitor
Frequent Visitor

Re: Calculated column based on measurements.


@richbenmintz wrote:

Hi @aguiardavid,

 

You have a couple of Issues:

1. Your Sales 2 does not have an active relationship to the Sales 1 Table, both seem to be fact tables related to Product. there is no way that the color you choose from Sales 2 will filter sales 1 column without a measure that specifically using that inactive relationship, even so it is related to the sales amount and does not really make sense to me.

2. Calculated Columns only refresh their value when the model is refresh, slicers and filtering have no effect on them, so even if the relationship between Sales 1 and Sales 2 were active and made sense the filtering would not change the behaviour of the Calc Column.

 

I have updated your Pbix file, added a color dimension, moved the sales 2 column into sales 1 fact table, added a measure that shows the result as this will get evaluated when filters and slicers are used

 

https://1drv.ms/u/s!AhCeuF2piSWMgcBSvkvZ8VPegI2cDg


 

hi @richbenmintz

 

As for item 1, that's right. The "Sales1" table has a granularity. The table "Sales2" has another granularity (I left the color in the 2, but imagine that it had other fields, totally different). The only common dimension is the "Product".

 

The idea is to apply filters, some in the "Sales1" table and others in the "Sales2" table, to compare the values. From these filters, generate the dynamic comparatives, on top of the aggregated information. That is, it does not have all of this being on the same table (this model is an example, in real data it is not necessary to leave everything together and ready in the table, because the filters would change the aggregated values).

 

As for item 2, if the calculated column only updates when update occurs, it will not work.

 

The question is to find some way to make this filter, perhaps with another dimension, that can have this classification or another filter that allows filter calculated measure.

richbenmintz
Advisor

Re: Calculated column based on measurements.

Hi @aguiardavid,

 

You understand that the Colour field in Table 2 will not filter Table 1 correct, the relationship is inactive. If you are going to have fact table with different granularty that is ok, however you still need to have a dimension for color to ensure that both facts are filtered by a single color attribute. 

If you looked at the file i provided you will see that i created a measure for you that determines the result based on the filters that the user selects.

 

Good Luck,

 

Richard

aguiardavid Frequent Visitor
Frequent Visitor

Re: Calculated column based on measurements.

hi @richbenmintz

 

I understand. The expected reaction is this. That is, to be able to compare the sale1 of Radio Black, with sale2 Red TV for example.

 

Just like to have a dynamic field, to get the products that have sale1 and sale2, according to the filters you make ...

 

It's difficult, but there must be a way.

richbenmintz
Advisor

Re: Calculated column based on measurements.

Hi @aguiardavid,

 

Did you look at the file i provided? I created a color dimension and created a measure that will dynamically set the warning or ok, based on the selected color and product filters