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.
In my table there is a grouping (Group 1,2,3) composed of several clients that are not repeated and the only field that is modified is the field "FACTOR".
Since my base client is client "E", and the value for the FACTOR field is not fixed, how to develop the DAX function using variables for the following case:
Help me!!!
Table:
Solved! Go to Solution.
Hi,
Maybe you could try these formulas.
Above = VAR E2014 = CALCULATE ( MIN ( Table1[Factor2014] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) VAR E2015 = CALCULATE ( MIN ( Table1[Factor2015] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) VAR E2016 = CALCULATE ( MIN ( Table1[Factor2016] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, 'Table1'[Factor2014] > E2014 || 'Table1'[Factor2015] > E2015 || 'Table1'[Factor2016] > E2016 ) )
Below = VAR E2014 = CALCULATE ( MIN ( Table1[Factor2014] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) VAR E2015 = CALCULATE ( MIN ( Table1[Factor2015] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) VAR E2016 = CALCULATE ( MIN ( Table1[Factor2016] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, 'Table1'[Factor2014] < E2014 || 'Table1'[Factor2015] < E2015 || 'Table1'[Factor2016] < E2016 ) )
Best Regards!
Dale
Hi,
Maybe you could try these formulas.
Above = VAR E2014 = CALCULATE ( MIN ( Table1[Factor2014] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) VAR E2015 = CALCULATE ( MIN ( Table1[Factor2015] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) VAR E2016 = CALCULATE ( MIN ( Table1[Factor2016] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, 'Table1'[Factor2014] > E2014 || 'Table1'[Factor2015] > E2015 || 'Table1'[Factor2016] > E2016 ) )
Below = VAR E2014 = CALCULATE ( MIN ( Table1[Factor2014] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) VAR E2015 = CALCULATE ( MIN ( Table1[Factor2015] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) VAR E2016 = CALCULATE ( MIN ( Table1[Factor2016] ), FILTER ( ALL ( Table1 ), 'Table1'[Clients] = "E" ) ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, 'Table1'[Factor2014] < E2014 || 'Table1'[Factor2015] < E2015 || 'Table1'[Factor2016] < E2016 ) )
Best Regards!
Dale
The solution it´s ok, great!!!!
I agree with the other posts suggesting you reshape your data to have a single factor column and a year date dimension.
Having said that I think you're after something like this, I created a table like yours called Factor...
Then I created a new column called "Clientes Factor" which totals the three factor values for each client:
Clientes Factor = CALCULATE(SUMX(factor, factor[Factor 2014]+factor[factor 2015]+factor[factor 2016]))
Then I created a new measure called "Factor E Calc" which is Client Es factor total for comparison:
Factor E Calc= calculate(SUMX(factor, factor[Factor 2014]+factor[factor 2015]+factor[factor 2016]), Factor[clientes]="E")
Then to work around a circular dependency error I created a new measure called "Factor E" which is equal to the value I entered manually (copying the value calculated in "Factor E Calc):
Factor E = 6.1
Then I created a new column comparing "Clientes Factor" with "Factor E" I called it Factor Rating:
Factor Rating = if(Factor[Clientes Factor]<Factor[Factor E], "Less", (if(Factor[Clientes Factor]=Factor[Factor E], "Same", "Higher")))
Here's what the resulting table looks like:
group | clientes | Factor 2014 | Factor 2015 | Factor 2016 | Clientes Factor | Factor Rating |
1 | A | 1 | 2 | 2.3 | 5.3 | Less |
1 | B | 1.5 | 3.4 | 1.3 | 6.2 | Higher |
1 | C | 2.3 | 1 | 1.7 | 5 | Less |
1 | D | 1.3 | 1.5 | 1.3 | 4.1 | Less |
2 | E | 1.7 | 2 | 2.4 | 6.1 | Same |
2 | F | 2 | 4 | 1.5 | 7.5 | Higher |
2 | G | 3.4 | 1.7 | 2.2 | 7.3 | Higher |
2 | H | 1 | 2.3 | 2 | 5.3 | Less |
3 | I | 1.5 | 1.3 | 2.4 | 5.2 | Less |
3 | J | 2 | 1.7 | 1 | 4.7 | Less |
3 | K | 4 | 2.4 | 2.4 | 8.8 | Higher |
3 | L | 1.7 | 4.7 | 2 | 8.4 | Higher |
I'm sure there's more elegant ways of achieving the same outcome but hopefully that helps 🙂
I find having 3 columns for Factor pretty awkward, but... here are some ideas to get you started?
Total Customers = COUNTROWS(MyTable)
Total Factor := SUMX(MyTable, MyTable[Factor 2014] + MyTable[Factor 2015] + MyTable[Factor 2016])
E Factor := CALCULATE([Total Factor], MyTable[Clientes] = "E")
Low Customers := CALCULATE([Total Customers], FILTER(MyTable, [Total Factor] < [E Factor])
High Customers := CALCULATE([Total Customers], FILTER(MyTable, [Total Factor] > [E Factor])
@vmsouza30 not sure if i understand your requirement, but maybe something like the earlier function can work?
http://tinylizard.com/dax-earlier-function/
there is also a later function. If you can work out a pattern with it.
Proud to be a Super User!
I think you are going to want to reshape your data. Likely you will find it easier to deal with a single factor column, and a separate year column. (You can use the Unpivot functionality in Edit Queries for this).
I'm also skeptical that you are going to want comma separated values like that...
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |