RankX Sum of Column and Slicer
Hello Everybody, I have a table with transactional records, that table has a counter of 1 per transaction by employee and the measure is an average of 0 and 1 from a different column. On my power BI I have a slicer that will filter locations. What i am trying to do is to rankx the average column by employee and filter out if the sum of transacions if is less than 30, whenever I do that the Rank will remain the same after I filter out less than 30 some of the formulas i tried: RANKX(ALLSELECTED(table[emp_name]), table[avg_metric]) RANKX(ALLSELECTED(table), table[avg_metric]) RANKX(ALLSELECTED(slicer_geo), table[avg_metric]) not all the ranks will be 31 but after I filter out the less than 30 it will hide some their rankings and not generate one for only the selected fields thart met the greater than 30 filter. Any recommendations?
Leave some values blank and Rank remaining items
I have only been using Power Pivot and Dax for 1-2 weeks. I've been trying to figure out how to return blanks for a couple of row values and rank over all remaining items in the column. I have spent the better part of 4 business days scouring the internet for an answer. I saw what appeared to be similar posts here in this MS PBI community. Those were more like if there is a blank leave it blank otherwise rank. When I tried various versions of Rankx and other functions I only got 1's for everything. I am using Power Pivot. So here is where I am at I think I am almost there with the formula below. Is there a shorter/better way of doing this? Most of the examples that I saw online used SELECTEDVALUE but I think that only applies to Power BI because I don't see it on my Power Pivot function list. Which is why I ended up using AND and hasonevalue, etc. I want it to rank over everything except the two values "Retirement" and "Other" but curretly it ranks everything thing first and then blanks out "Retirement" and "Other" values. How do I rewrite my formula so it does what I want? Note I am using all sections: Filters, Columns, Rows, Values and plan to use multiple slicers. Whatever solution is provided should keep this in mind and be dynamic. Measure: Responses =counta([EMPLID]) Measure: RankExclTEST: =IF(AND(IF ( HASONEVALUE ( LAPIS_EXIT_LOAD[ITEM_DESCR] ), VALUES (LAPIS_EXIT_LOAD[ITEM_DESCR]) ) <>"Other", IF ( HASONEVALUE ( LAPIS_EXIT_LOAD[ITEM_DESCR] ), VALUES (LAPIS_EXIT_LOAD[ITEM_DESCR]) ) <>"Retirement" ) ,RANKX(ALL(LAPIS_EXIT_LOAD[ITEM_DESCR]),CALCULATE([Responses]),,0),blank()) Pivot table screenshot. Note I am using all sections: Filters, Columns, Rows, Values and plan to use multiple slicers. Data Model
