The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
First of all, pardon me if the subject title is a little misleading but I found it hard to describe the problem effectively as a title.
First off, some background information:
I have a table with an index column and some values that's filtered by a slicer. This means that the index values might not come in a normal order, and can e.g look like below:
Table1:
Index | Value |
1 | Test value 1 |
3 | Test value 3 |
4 | Test value 4 |
7 | Test value 7 |
20 | Test value 20 |
My objective based on this as of right now, is to have 5 measures (in this situation) that each holds "the next minimum value based on the previous measure". However, when attempting this (see formula below) I'm getting a "blank" value (checked when inserting to a card).
min val 1 = min('Table1'[Index])
min val 2 = calculate(min('Table1'[Index]);FILTER('Table1';'Table1'[Index]>[min val 1]))
Substituting the "[min val 1]" measure part from the "min val 2" measure statement gives the correct result of 3, however using the measure just leaves it blank.
Isn't it possible to create/use/have such dependencies ?
Solved! Go to Solution.
You can do this, the issue is that inside your Filter() call the value for the [min val 1] measure is recalculated for each row. This effectively filters out every row as the min value for a single row is the value of the index for that row.
The simple fix is to use a variable to capture the value of the measure outside of the filter.
min val 2 = var _min_val_1 = [min val 1] return calculate(min('Table1'[Index]),FILTER('Table1','Table1'[Index]> _min_val_1))
You can do this, the issue is that inside your Filter() call the value for the [min val 1] measure is recalculated for each row. This effectively filters out every row as the min value for a single row is the value of the index for that row.
The simple fix is to use a variable to capture the value of the measure outside of the filter.
min val 2 = var _min_val_1 = [min val 1] return calculate(min('Table1'[Index]),FILTER('Table1','Table1'[Index]> _min_val_1))
User | Count |
---|---|
87 | |
84 | |
36 | |
35 | |
30 |
User | Count |
---|---|
95 | |
74 | |
67 | |
52 | |
51 |