Logic / Dax require

[PowerBIBeginer]

Hi All,

Query which I'm asking I'm not sure whether its possible in power bi or not. Let me narrate the exact condition.

Col A - Customer name

Col B - Line of Business (could be anything)

Col C - Type (either Primary or Excess Or NLP)

Example:

Customer Name	LoB	Type
Piramal Ind	Health	Primary
Piramal Ind	Health	Excess
Nike	Sports	Excess
Tata	Transport	Primary
Tata	Food	Excess
Tata	Food	Primary
Piramal Ind	Food	Excess
SkyBag	Lifestyle	Primary
SkyBag	Lifestyle	Excess
SkyBag	Entertainment	NLP
SkyBag	Entertainment	Excess

If Customer Name and LoB same then check for Type.

Copy Excess type cases only when customer and LoB have primary type. If no primary type and any record have excess type records, skip those. Nothing to do with NLP type.

This is my query. Is this possible at all in power bi? KIndly Suggest..

[lbendlin]

Please show the expected outcome based on the sample data you provided.

[PowerBIBeginer]

Thank You for your reply and time share on my query.

Customer Name	LoB	Type	comments
Piramal Ind	Health	Primary
Piramal Ind	Health	Excess	Copy this row and paste in different table and should get delete from this current tabel
Nike	Sports	Excess
Tata	Transport	Primary
Tata	Food	Excess	Copy this row and paste in different table and should get delete from this current tabel
Tata	Food	Primary
Piramal Ind	Food	Excess
SkyBag	Lifestyle	Primary
SkyBag	Lifestyle	Excess	Copy this row and paste in different table and should get delete from this current tabel
SkyBag	Entertainment	NLP
SkyBag	Entertainment	Excess

Output will look like..

Current table-

Customer Name	LoB	Type
Piramal Ind	Health	Primary
Nike	Sports	Excess
Tata	Transport	Primary
Tata	Food	Primary
Piramal Ind	Food	Excess
SkyBag	Lifestyle	Primary
SkyBag	Entertainment	NLP
SkyBag	Entertainment	Excess

and another table wil add those deleted rows

Customer Name	LoB	Type
Piramal Ind	Health	Excess
Tata	Food	Excess
SkyBag	Lifestyle	Excess

[Anonymous]

Hi @PowerBIBeginer ,

I made simple samples and you can check the results below:

Measure = var _t = ADDCOLUMNS('Table',"Same",COUNTAX(FILTER(ALL('Table'),[Type]<>"NLP"&&[Customer Name]=EARLIER([Customer Name])&&[LoB]=EARLIER([LoB])),[Type]))
var _t2 = IF(MAX('Table'[Type])="Excess",1,0)
RETURN IF(MAXX(_t,[Same])<>1&&_t2=1,1,0)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[PowerBIBeginer]

Hi, the post where you share screenshot in that we can clearly see that separation happen correctly. But it dosent work on my table..😔 the logic which I said, if same account and LOB and contains Primary and Excess types, then remove excess to the other table, that is not happening.

[Anonymous]

Hi @PowerBIBeginer ,

So what results are you getting now and are there any other relationships, please provide more information.

Best regards,
Community Support Team_ Scott Chang

[PowerBIBeginer]

Hi, one query please.. How do I add this dax in "Transform Data - applied steps"? I'm basically working there on eliminating steps. Do I need to create this in "Advance editor" under the steps the advaance editor? Pls suggest

[lbendlin]

You don't. DAX code is used in the User interface phase. Transform steps are written in Power Query, in the data acquisition and transformation stage. Those are separate processes.

[PowerBIBeginer]

Thank You so much! Very exceited looking at your reply and highly appriciate your time and responce. I highly appriciate You and all the Fabric Community. Sir, I will check this solution and will revert you.