Tableau Fixed LOD (applied on a column) conversion to Power BI

[Anonymous]

Hi All,

We have a table like this below 

Brand	Product	Corporation
A	X	E
A	A	F
A	Z	G
A	U	H
A	V	I
B	R	E
B	B	F
B	X	G
B	Y	H
B	Z	I

I wanted to convert the below tableau formula to Power BI

{ FIXED [Market]:MAX([Market]=[Product] AND [Corporation]='F') - this formula allows to check the other competitor products and corporation for that Market which satisfies the condition because of LOD

I have tried to apply the same by creating a flag in Power BI

condition check = IF( 'Table'[Market]='Table'[Product]&&'Table'[Corporation]='F' ,1,0)

but this filters the specific row , so I am unable to check other products within the Brand.

Please help how to achieve this 

Thanks,

Ananth 

[Anonymous]

Hi Team ,

Sorry for the delay in response. 

Actually I have made some naming mistake while mentioning the formula .

Thats not "Market" , it is "Brand"

{ FIXED [Brand]:MAX([Brand]=[Product] AND [Corporation]='F') . - this formula gives the brand names which satisfy the condition brand = product and corporation = F

the desired output is

Brand	Product	Corporation	desired output	Sales
A	X	E	A	500
A	A	F	A	400
A	Z	G	A	300
A	U	H	A	200
A	V	I	A	100
B	R	E	B	500
B	B	F	B	400
B	X	G	B	300
B	Y	H	B	200
B	Z	I	B	100
C	X	P	Blank	500
C	Y	Q	Blank	400
C	Z	R	Blank	300
C	F	S	Blank	200
C	K	T	Blank	100
C	R	U	Blank	9

Brand A and B satisfy the condition and C did not (so blank values)

once I got this , I will take the "Desired output" field , and rank accordingly to the "sales" column ,

Product	Corporation	desired output	Sales	Rank
X	E	A	500	1
A	F	A	400	2
Z	G	A	300	3
U	H	A	200	4
V	I	A	100	5
R	E	B	500	1
B	F	B	400	2
X	G	B	300	3
Y	H	B	200	4
Z	I	B	100	5
X	P	Blank	500	Blank
Y	Q	Blank	400	Blank
Z	R	Blank	300	Blank
F	S	Blank	200	Blank
K	T	Blank	100	Blank
R	U	Blank	9	Blank

I have tried the below dax 

Calculate (

MAXX('Table',IF( CONTAINSSTRING('Table'[Brand],'Table'[Product])&&'Table'[Corporation]="F" ,1,0) ),
ALLEXCEPT('Table','Table'[Brand])

will apply filter 1 for the above dax calculated column

apply rank filter =1 , so we will get competitor product and corporation information

Product	Corporation	desired output	Sales	Rank
X	E	A	500	1
R	E	B	500	1

rank formula = 

var result = 

rankx(

Filter(

all('Table'[Product],'Table'[Corporation]),
'Table'[Corporation]<>"F"),//in competitor table does not have our own corporation product so that excluded "F"

[Sales],,,Dense)

return result

Applied the filter on this "rank formula" =1

It is loading long time to run and some times failing with "Not enough memory". (Please note : I have single Fact table and the PBI size is 1.2 GB having Premium workspace , in Power BI Service as well same problem )

Please check and let me know, is there any way to achieve this.

Thanks,

Ananth

[Anonymous]

Hi @Anonymous ,

Any updates?

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @Anonymous ,

What does this mean?——but this filters the specific row , so I am unable to check other products within the Brand.

And you used 'Table'[Market] ,which is not matched with the Table you provided, is it Brand column?

As @amitchandak mentioned, please share more detail information to help us clarify your scenario.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

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

[amitchandak]

@Anonymous , I do not see Market and Corporation =B in your data.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.