Composite Key Error & Top Values

[SudharsanRaja]

USECASE:

I created parameter for product, country and segment.

* When I clicked the product, it has to display profit greater than 180. And top 2 sales in descending order.
* When I clicked the Country, it has to display profit greater than 180. And top 2 sales in descending order.
* When I clicked the Segment, it has to display profit greater than 180. And top 2 sales in descending order.
* Both profit and sales are measure created by me with DAX function.
* When I drag the rank function in visual it is showing composite key error. Which is showing below.

Sales_Measure = DIVIDE(SUM(financials[ Sales]),100000)
Profit_Measure = DIVIDE(SUM(financials[Profit]),10000)

Rank__ = SWITCH(
TRUE(),
SELECTEDVALUE(Parameter[Parameter])="'financials'[Country]",
RANKX(ALLSELECTED(financials[Country]),
[Sales_Measure],,DESC),
SELECTEDVALUE(Parameter[Parameter])="'financials'[Product]",
RANKX(ALLSELECTED(financials[Product]),
[Sales_Measure],,DESC),
SELECTEDVALUE(Parameter[Parameter])="'financials'[Segment]",
RANKX(ALLSELECTED(financials[Segment]),
[Sales_Measure],,desc))

How to overcome this error to display top 2 sales values and also profit greater than 180 using parameter selection?

[MFelix]

Hi @SudharsanRaja ,

The question is the context of the measure and the filter context that you are using since the measure is done on top of allselected only after doing that the values will be filter by the 180 doing the incorrect calculation you need to have the filter context of the values on the measure also.

Try to change your calculation to something similar to this:

RANKX(
FILTER( ADDCOLUMNS(ALLSELECTED(financials[Country]), "TotalSales",[Sales_Measure]), [TotalSales] > 180)
,
[TotalSales],,DESC)

[MFelix]

Hi @SudharsanRaja ,

for the usage of the parameter values in SELECTEDVALUE format you need to use the Order Column where you have the or add a new column to your table that is a duplication of the first column that you want to use.

If you change your measure to the folllowing code it should work:

Rank__ = SWITCH(
TRUE(),
SELECTEDVALUE(Parameter[Order])=0,
RANKX(ALLSELECTED(financials[Country]),
[Sales_Measure],,DESC),
SELECTEDVALUE(Parameter[Order])=1,
RANKX(ALLSELECTED(financials[Product]),
[Sales_Measure],,DESC),
SELECTEDVALUE(Parameter[Order])=2,
RANKX(ALLSELECTED(financials[Segment]),
[Sales_Measure],,desc))

 I'm assuming that the order of your parameter is Country, Product, Segment

[SudharsanRaja]

Thanks @MFelix . Composite key error is resolved! Rank function is executing first and then it is filtering the profit value greater than 180. But in my case it needs to execute in reverse order! Do you have any suggestions?

[MFelix]

How are you doing the filtering for the 180? In the syntax you present there is no filter on the dax measures.

[SudharsanRaja]

In dax measure I am not creating any filtering for 180. In visual filter I am using profit greater than 180!