Power DAX recommend at least one product that has the same price or higher.

[PatrickNahayo]

I have the following table information. depicting the concept that i am dealing with. However, i have not been able to achieve any DAX calculations that can show the scenarios i depict below.

article id	article name	price	available
1	a	10	x
2	b	15
3	c	12	x
4	d	8	x
5	e	10	x
6	f	20	x
7	g	18
8	h	5

i want the users to achieve the following scenarios
1. If the product is available, show it as it is.

2. If the product is not available do the following:

  2.1 recommend a product that is available with the same price

  2.2 recommend a product that is avaible with a higher price

This can be another column in the table or just shown on the fly if the user select a product.

Thank you very much for your help
@PowerBI 

[PatrickNahayo]

@ryan_mayu @Ashish_Mathur thanks very much for the assistance, however, i want that there is a recommendation of more than one product. and I have tried with the CONCATENEX but to no results.

I want to achieve the following results

article id	article name	category	country	price	available	recommended
1	a	aa	1a	10		b,c
2	b	aa	1a	15	x
3	c	aa	1a	12	x
4	d	aa	1a	8	x
5	e	bb	2a	10		f,i
6	f	bb	2a	20	x
7	g	bb	2a	18		i
8	h	bb	2a	5	x
9	i	bb	2a	20	x

[Ashish_Mathur]

Hi,

This calculated column formula works

Articles = VAR _cat = Data[category]
            var _ctry = Data[country]
            var _price = Data[price]
            var _id = Data[article id]
VAR vTable =
    CALCULATETABLE (
        VALUES (Data[article name]),
        Data[category] = _cat,
        Data[country] = _ctry,
        Data[price]> _price,
        Data[available]="X",
        Data[article id]>_id,
        ALL (Data)
    )
VAR vResult =
    if(Data[available]="X",BLANK(),CONCATENATEX ( vTable, Data[article name], ", " ))
RETURN
    vResult

Hope this helps.

[PatrickNahayo]

@Ashish_Mathur thanks very much for the help on this. However, i'm getting an out of memory error. Any idea on how to solve this?

[Ashish_Mathur]

Sorry, i cannot help with that.

[PatrickNahayo]

@ryan_mayu thanks very much for this great example, and i'm sure it will be of great help in the future.
It has helped learn about new functions as well how to use variables in my DAX.
However, on detailed i missed is that the product can only be recommended, if the category is the same and is available in the same country.

article id	article name	category	country	price	available	recommended
1	a	aa	1a	10		b
2	b	aa	1a	15	x
3	c	aa	2a	12	x
4	d	aa	2a	8	x
5	e	bb	3a	10		f
6	f	bb	3a	20	x
7	g	bb	4a	18		i
8	h	bb	4a	5	x
9	i	bb	4a	20	x

I have tried multiple IFs but to no success. The outcomes should look like this in the table above.

[ryan_mayu]

@PatrickNahayo 

pls try this

Column = 
VAR _pro=minx(FILTER('Table','Table'[price]>=EARLIER('Table'[price])&&'Table'[available]="x"&&'Table'[country]=EARLIER('Table'[country])&&'Table'[category]=EARLIER('Table'[category])),'Table'[price])
return if('Table'[available]="x","",minx(FILTER('Table','Table'[available]="x"&&'Table'[price]=_pro&&'Table'[category]=EARLIER('Table'[category])&&'Table'[country]=EARLIER('Table'[country])),'Table'[article name]))

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = if(Data[available]=BLANK(),CALCULATE(MIN(Data[article name]),FILTER(Data,Data[category]=EARLIER(Data[category])&&Data[country]=EARLIER(Data[country])&&Data[available]="x"&&Data[price]>=EARLIER(Data[price]))),BLANK())

Hope this helps.

[ryan_mayu]

@PatrickNahayo 

is this what you want?

Column = 
VAR _pro=minx(FILTER('Table','Table'[price]>=EARLIER('Table'[price])&&'Table'[available]="x"),'Table'[price])
return if('Table'[available]="x",'Table'[article name],minx(FILTER('Table','Table'[available]="x"&&'Table'[price]=_pro),'Table'[article name]))