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.
Dear Colleagues, I'm looking for a simple solution to use the DAX measures to get the index based on the first value.
The example is below with the Index value targeted as results. It needs to change dynamicaly depending on the Date.
I have been trying firstnonblank but it doesnt give the results when the Dates are visible.
It would be great if somebody can assist, the PBI file is available in this link = PBI Link
Date | Value | Index |
Jan-19 | 150 | 100 |
Feb-19 | 160 | 107 |
Mar-19 | 165 | 110 |
Apr-19 | 177 | 118 |
May-19 | 178 | 119 |
Jun-19 | 178 | 119 |
Jul-19 | 200 | 133 |
Aug-19 | 220 | 147 |
Sep-19 | 224 | 149 |
Oct-19 | 190 | 127 |
Nov-19 | 180 | 120 |
Dec-19 | 190 | 127 |
Jan-20 | 220 | 147 |
Feb-20 | 200 | 133 |
Mar-20 | 200 | 133 |
Apr-20 | 230 | 153 |
May-20 | 240 | 160 |
Jun-20 | 250 | 167 |
Jul-20 | 260 | 173 |
Aug-20 | 220 | 147 |
Solved! Go to Solution.
hi, @leandroduarte
First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Then you could use RANKX Function to create a measure
Measure = RANKX(ALLSELECTED(Table1),CALCULATE(SUM(Table1[Amount])),,ASC,Dense)
Result:
Best Regards,
Lin
hi, @leandroduarte
First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Then you could use RANKX Function to create a measure
Measure = RANKX(ALLSELECTED(Table1),CALCULATE(SUM(Table1[Amount])),,ASC,Dense)
Result:
Best Regards,
Lin
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |