How to use (Ntile for SQL server) or Percentiles of DAX properly when used on selected filters?

[Anonymous]

Hi all,

I have below data for 6-weeks for 6-users with 4-specailty here

Expected Output:

when selected one of the specialty, the Ntile should work properly ..say n = 4.

I am trying to extract users on basis of selected specailty and provide a ntile score.

I have went through this answer     but that I couldnt understand how it works.

In SQL server, I am able to do it correctly using NTILE function with partition by specailty.

Please someone explain me the logic in DAX(mostly needed a measure as I have around 0.5M userIDs with around 4M of rows)

Thanks in Advance!!

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your link to this function in SQL Server, I guess this is a function which can dynamically group the data of a column in order, I could not find the related function in DAX library, but I guess the group and bin in Power BI can achieve this properly, you can take a look:

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

You can also go to the DAX library to find if there is a function like Ntile() for SQL server:

https://docs.microsoft.com/en-us/dax/dax-function-reference

Best Regards,

Community Support Team _Robert Qin

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

[v-robertq-msft]

Hi, @Anonymous 

According to your description and expected output, I think you want to get a measure which displays the rank of [S-Score] group by [specailty], you can try this measure:

Expecting =

RANKX(

    FILTER(

        ALLSELECTED('Table'),

    [Specialty]=MAX([Specialty])),

    CALCULATE(MAX('Table'[S-Score])),,

    DESC,

Dense )

Then create a table chart and place columns and measure like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[Syndicate_Admin]

Hello, where is the link of your test board? Thank you

[Anonymous]

Hi buddy, thanks for replying to my question. Actaully Its not about rank..I am looking for equivalent of  sql server Ntile()  function in powerBI.  I have update my screenshot.. can you please help me !!

[Syndicate_Admin]

Hello I hope you are well, you found a solution?