Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculated column based on measurements.

Please help me!

I'm needing a filter based on value in one measure.

I have a dimension "Client" which has 2 related tables. Sale and Potential. The relationship of these tables is 1 -> N.

I need to create a rating, being:

Has potential and has sale: OK
Has potential and has no sale: attention.

This needs to be done at the aggregate level (total client) and filter, in order to filter clients "Ok" and "Attention".

I made the measure calculated like this:
LegendMap = if ([qtd_cli_potential]> 0 && [qtd_cli_pot_vend]> 0; "Ok"; "Warning")

Setting up a table, placing the "Client" and this Map Legend field, works. However, I need to put it as a filter, where I can choose. And I can not add calculated measure to the filter.

I imagine the solution is to include a calculated column in the Customer dimension that makes the rule above. I tried to do, but it only makes over the total, brings the overall total to all customers. can you help me?
17 REPLIES 17

Yes, add it as a calculated column in he client table. I see no reason why it won’t work if you copy it exactly as you have shown. If you are replacing the measures in the formula with the underlying formula, then you will need to add CALCULATE around the formulas. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi @Anonymous,

 

As Matt explains, you need to wrap your sums in Calculate like so

 

Column = if(CALCULATE(sum(Table2[qtd_cli_potential]))>0 && CALCULATE(SUM(Table2[qtd_cli_pot_vend]))>0,
"Ok"; "Warning")

Richard

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Anonymous
Not applicable

dont work.

 

All rows "ok".

 

1 row ok... 2 row ok... 3 row ok.... but 4, 5 and 6 row no...

 

 

print.png

Hi @Anonymous,

 

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")

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Anonymous
Not applicable

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...

 

 

Hi @Anonymous,

 

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Anonymous
Not applicable


@richbenmintz wrote:

Hi @Anonymous,

 

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.

Hi @Anonymous,

 

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Anonymous
Not applicable

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.

Hi @Anonymous,

 

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helle @Anonymous,

 

Try this forumule;

 

Result = 
VAR Sales1 = SUM(Table1[Sales 1])
VAR Sales2 = SUM(Table1[Sales 2])
RETURN
IF( Sales1 && Sales2>0;
"OK";
"WARNING")

Naamloos.png

 

Greets,

 

Ronald

Anonymous
Not applicable


@Ronald123 wrote:

Helle @Anonymous,

 

Try this forumule;

 

Result = 
VAR Sales1 = SUM(Table1[Sales 1])
VAR Sales2 = SUM(Table1[Sales 2])
RETURN
IF( Sales1 && Sales2>0;
"OK";
"WARNING")

Naamloos.png

 

Greets,

 

Ronald


 

If calculated measure, ok.

 

but calculated column, no.

 

I just need to have a field where I can filter the "IDs" OK or Warning.

 

print.png

Hello @Anonymous,

 

Try this forumule in a calculated colum;

 

Result = 
VAR Sales1 = IF(Table1[Sales 1]<>0;1)
VAR Sales2 = IF(Table1[Sales 2]<>0;1)
VAR TotalScore = Sales1+Sales2
RETURN
IF(TotalScore=2;"OK";
"WARNING")

Greets,

 

Ronald

Anonymous
Not applicable


@Ronald123 wrote:

Hello @Anonymous,

 

Try this forumule in a calculated colum;

 

Result = 
VAR Sales1 = IF(Table1[Sales 1]<>0;1)
VAR Sales2 = IF(Table1[Sales 2]<>0;1)
VAR TotalScore = Sales1+Sales2
RETURN
IF(TotalScore=2;"OK";
"WARNING")

Greets,

 

Ronald


 

 

did not work either.

 

The problem is that both fields need to be aggregated, because the values ​​we saw are the sum.

 

When I create a calculated column the sum of a field is being the total of the table. see the image.

 

if I can get this value to get the filters and the "id", I believe it will work on the filter.

 

print.png

@Anonymous,

 

Check the file, the calculated colum can be used as filter.

 

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

 

Greets,

 

Ronald

 

 

Anonymous
Not applicable

Anonymous
Not applicable


@Ronald123 wrote:

@Anonymous,

 

Check the file, the calculated colum can be used as filter.

 

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

 

Greets,

 

Ronald

 

 


Ronald123
Thank you for your help.

The example you sent has no aggregation. I think that's why.

Imagine that "Sale 1" was a separate table, in which "radio" had another column, for example "color". I would have to add, to sort later.

The 2 tables I'm using have different granularities. So what I show in the table, is added by sum (in 2). In your case, there is no aggregation, so I believe it works.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.