cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leandroduarte Frequent Visitor
Frequent Visitor

Create a Index comparison based on first row selected

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

 

DateValueIndex
Jan-19150          100
Feb-19160          107
Mar-19165          110
Apr-19177          118
May-19178          119
Jun-19178          119
Jul-19200          133
Aug-19220          147
Sep-19224          149
Oct-19190          127
Nov-19180          120
Dec-19190          127
Jan-20220          147
Feb-20200          133
Mar-20200          133
Apr-20230          153
May-20240          160
Jun-20250          167
Jul-20260          173
Aug-20220          147
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create a Index comparison based on first row selected

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:

2.JPG

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Create a Index comparison based on first row selected

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:

2.JPG

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.