Leave some values blank and Rank remaining items

[lopez235]

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

1. 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.
2. 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?
3. 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

[Anonymous · Accepted solution]

// You should stick to Best Practices if
// you want to have an easy time creating
// models and measures. Currently, you're
// not doing this, so don't be surprised
// if your model starts producing wrong
// figures from good formulas (when used
// in Power BI). I'd strongly suggest that
// you first familiarize yourself with
// the techniques of dimensional data modeling.
// In this badly designed model, this is the
// measure you're after but you take the
// responsibility for the wrong numbers
// it's going to produce IF you're going to
// use it in Power BI. If you stick to Excel
// ONLY, you're safe. It's a long story...
// but it boils down to the fact that Excel
// uses MDX to query models and Power BI
// uses DAX and in DAX you can be bitten
// by the so-called "auto-exist problem."

[Measure] =
IF( HASONEVALUE( LAPIS_EXIT_LOAD[ITEM_DESCR] ),
    var CurrentlyVisibleItemsCount =
        DISTINCTCOUNT( LAPIS_EXIT_LOAD[ITEM_DESCR] )
    var OnlyOneItemVisible =
        CurrentlyVisibleItemsCount = 1
    var Rank_ =
        IF( OnlyOneItemVisible,
            var Item_ = 
                DISTINCT( LAPIS_EXIT_LOAD[ITEM_DESCR] )
            var IllegitimateItems =
                {"Other", "Retirement"}
            var ItemIsLegitimate =
                NOT ( Item_ IN IllegitimateItems )
            var Output =
                if( ItemIsLegitimate,
                    var AllLegitimateItems =
                        FILTER(
                            ALL( LAPIS_EXIT_LOAD[ITEM_DESCR] ),
                            NOT (
                                LAPIS_EXIT_LOAD[ITEM_DESCR]
                                    IN IllegitimateItems
                            )
                        )                
                    var RankCalculation =
                        RANKX(
                            AllLegitimateItems,
                            [Responses],,
                            // you can change these two
                            // if you need to rank them
                            // in a different way
                            DESC, DENSE
                        )
                    return
                        RankCalculation
               )
            RETURN
                Output
        )
    return
        Rank_
)

[lopez235]

BTW your measure/solution seems to work. Only thing is initially it gave me an error that I had an invalid formula in my pivot table and it also seemed to have forced a reload from Power Query (ODBC queries). I ignored the message and added the measure and it worked.

I tested it with different slicers and it returns the results I expected. Thanks!

[Anonymous]

Good that it works... but as I said, if you don't refactor the model into a proper star schema and use this model in Power BI (and most likely one day you will), you're risking wrong figures without even being aware. They will come from good formulas, what makes it worse. And they will be random, which makes then even "more worse."

If you want to know why, you can watch this: https://www.sqlbi.com/tv/auto-exist-on-clusters-or-numbers-unplugged-22/ and then read the comments below the material.

Cheers.

[lopez235]

I agree, I need a better data model. I know about dimensional data modeling. I just haven't had a lot of hands-on experience. I was both project lead and business analyst for a project that migrated our HR BI enviornment from OBIEE to MicroStratregy. 

My original data model for the dataset that I posted about here was actually worse: it was one table! I had already built about 40 pvt tbls across 11 sheets using the same data model. I'm using a pair ODBC-based Power Queries loaded to the data model. I had to methodically transition my 1 table model to this two table model. It took me the better part of two days because I didn't want to recreate all my pivots since I had conditional formatting on several of them and had renamed all of them and I had slicers. I wish I had the foresight to have created an items dimension. Initially I wasn't too concerned with going "pure star schema" mode mainly because I only have a couple thousand rows of data over the course of 6 years.  I thought I was doing a smart thing by keeping it simple given the size of the data. I didn't account for what DAX would prefer.  You live and learn.

Am I correct in assuming that if I had an items dimension the syntax/solution would be less complex?