Forum Discussion

masmith004's avatar
masmith004
Regular Visitor
4 years ago
Solved

Dynamic Index Measure

Hello,

 

I am struggling with the idea of making a dynamic index measure using DAX in Power BI that would depend on multiple filters. For example, I have data that looks like this:

 

If I filtered this data to show Facility C, with Color = Green and Material = Cotton, I would like to have an index that looked like this:

Similarly, if I filtered the data to show Facility A and Color = Blue and Red, I would like to have an index that looked like this:

Is this possible? Thanks in advance

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your question correctly.

    I think it is better to know the sort order of each column, otherwise I suggest having a number column like the attached pbix file, and use RANKX function and ALLSELECTED function to create a index measure like below.

     

     

    Index measure: = 
IF (
    HASONEVALUE ( Data[Number] ),
    RANKX ( ALLSELECTED ( Data ), CALCULATE ( SUM ( Data[Number] ) ),, ASC )
)

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly.

    I think it is better to know the sort order of each column, otherwise I suggest having a number column like the attached pbix file, and use RANKX function and ALLSELECTED function to create a index measure like below.

     

     

    Index measure: = 
IF (
    HASONEVALUE ( Data[Number] ),
    RANKX ( ALLSELECTED ( Data ), CALCULATE ( SUM ( Data[Number] ) ),, ASC )
)