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.
Hello,
I have a table like the one below, showing the minimum freight cost for a specific route based on the origin. Each origin is a different column.
In my database I already have the best origin for each specific route, but I want to create something more dynamic.
The idea was to make the Power BI recalculate the best origin based on the visible columns only.
For example, in a database where I have the route A, B, C, D, E and origin F1, G1, H1, I1, J1, my database shows the best route as H1:
F1 | G1 | H1 | I1 | J1 | Best origin | |
A | 967 | 875 | 825 | 843 | 849 | H1 |
B | 947 | 843 | 974 | 940 | 825 | J1 |
C | 939 | 820 | 971 | 960 | 875 | G1 |
D | 860 | 819 | 986 | 827 | 937 | G1 |
E | 886 | 853 | 835 | 877 | 898 | H1 |
But, for the route A, I want power BI to consider I1 as the best origin if I remove H1 from the table
F1 | G1 | I1 | J1 | Best origin | |
A | 967 | 875 | 843 | 849 | I1 |
I recently found a formula that brings me the best origin, but how should I adapt it in order to make it consider only the visible columns of a table that I will create?
Column = VAR temp = { ( "Anchor1", Table1[Anchor_1] ), ( "Anchor2", Table1[Anchor_2] ), ( "Anchor3", Table1[Anchor_3] ), ( "Anchor4", Table1[Anchor_4] ) } VAR MinValue = MINX ( FILTER ( temp, [Value2] <> 0 ), [Value2] ) RETURN MINX ( FILTER ( temp, [Value2] = MinValue ), [Value1] )
Thank you
Hello,
I have a table like the one below, showing the minimum freight cost for a specific route based on the origin. Each origin is a different column.
In my database I already have the best origin for each specific route, but I want to create something more dynamic.
The idea was to make the Power BI recalculate the best origin based on the visible columns only.
For example, in a database where I have the route A, B, C, D, E and origin F1, G1, H1, I1, J1, my database shows the best route as H1:
F1 | G1 | H1 | I1 | J1 | Best origin | |
A | 967 | 875 | 825 | 843 | 849 | H1 |
B | 947 | 843 | 974 | 940 | 825 | J1 |
C | 939 | 820 | 971 | 960 | 875 | G1 |
D | 860 | 819 | 986 | 827 | 937 | G1 |
E | 886 | 853 | 835 | 877 | 898 | H1 |
But, for the route A, I want power BI to consider I1 as the best origin if I remove H1 from the table
F1 | G1 | I1 | J1 | Best origin | |
A | 967 | 875 | 843 | 849 | I1 |
I recently found a formula that brings me the best origin, but how should I adapt it in order to make it consider only the visible columns of a table that I will create?
Column = VAR temp = { ( "Anchor1", Table1[Anchor_1] ), ( "Anchor2", Table1[Anchor_2] ), ( "Anchor3", Table1[Anchor_3] ), ( "Anchor4", Table1[Anchor_4] ) } VAR MinValue = MINX ( FILTER ( temp, [Value2] <> 0 ), [Value2] ) RETURN MINX ( FILTER ( temp, [Value2] = MinValue ), [Value1] )
Thank you
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |