Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Team,
I am facing an issue with a dax measure being used as a value in a matrix which has field A in rows and B in columns.
I want to get the minimum value from A which can be selected from slicer and subtract it with each value of A.
My output should look like -
A | B | Value | Min | Result |
FR | CK | 9.27 | 5.76 | 3.51 |
Sp | CK | 8.88 | 5.76 | 3.12 |
It | CK | 6.25 | 5.76 | 0.49 |
Ro | CK | 5.76 | 5.76 | 0,00 |
The DAX measure I have written is to calculate Min value which should change based on the country A slicer.
Min =
VAR Sel = VALUES('Dim Table'[A])
VAR _1 =
SUMMARIZE (
FILTER ( ALL('Dim Table') , 'Dim Table'[A] IN Sel ),
'Dim Table'[A],
"Net Val",
[ValueMeasure]
)
VAR _2 =
ADDCOLUMNS (
_1,
"MINVAL",
MINX (
FILTER (
_1,
[Net Val] < EARLIER ( [Net Val] ) && [Net Val] <> 0
),
[Net Val]
)
)
VAR _3 =
MINX ( _2, [MINVAL] )
RETURN
IF (
[ValueMeasure] = 0,
0,
_3
)
This just gives me right minimum total but not minimum value shown against each country in column A. This makes it difficult for me to calculate Result
Any idea on what needs to be done to show same minimum (5.76 as per above example) against each A as the minimum [ValueMeasure] from the slicer selection? if Ro is not selected from slicer then minimum value should be 6.25 to be displayed against each row.
In case something is not clear, please ask. Thank you!
Hi @askpbiuser so you want Minimal value per column B in calculated column as solution or as measure?
Proud to be a Super User!
I want it as a measure because Value is a measure and I need Min Required per column B as a measure so that I can use it for next calculation which is Value - Min Required.
So for example for first row it would be 2.46 - 1.98 (Value - Min Required)
For second row for BE , it would be 4.7 - 3.58 and so on. This is as you rightly said per column B.
In a matrix, B is in columns, A is in rows and min required in values
Hope you got the point, else feel free to ask.
Hi @askpbiuser below is measure, adjust Sheet1 to your table name
Output
Did I answer your question? Kudos appreciated / accept solution!
Proud to be a Super User!
Hi @askpbiuser below is measure, adjust Sheet1 to your table name
Output
Did I answer your question? Kudos appreciated / accept solution!
Proud to be a Super User!
Hi @askpbiuser you need to calculate minimal value per column A?
If yes, try measure below. Adjust Sheet1 to your table name. I add some data to show you as example
Output
Did I answer your question? Kudos appreciated / accept solution!
Proud to be a Super User!
Thank you so much and sorry if I wasn't clear in my question.
I tried your approach and I get the result as Min Now column (next to value) , but what output I need is column Min Required.
Basically for each column B whatever is minimum value in column A becomes the min required.
For example - if filtered on value P in column B then , minimum should be 1.98 assigned to each row.
Let me know if you have any further question.
User | Count |
---|---|
54 | |
48 | |
19 | |
16 | |
15 |
User | Count |
---|---|
115 | |
42 | |
42 | |
27 | |
22 |