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.
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?
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.
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
Proud to be a Super User!
dont work.
All rows "ok".
1 row ok... 2 row ok... 3 row ok.... but 4, 5 and 6 row no...
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")
Proud to be a Super User!
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
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
Proud to be a Super User!
@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
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
Proud to be a Super User!
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
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")
Greets,
Ronald
@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")
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.
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
@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.
@Anonymous,
Check the file, the calculated colum can be used as filter.
https://1drv.ms/u/s!An5X7wdH9O9eiiM2VftWnjWfBTCK
Greets,
Ronald
I adapted the file you sent. Look at the problem.
@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.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |