topic Logic / Dax require in DAX Commands and Tips

Logic / Dax require

PowerBIBeginer — Fri, 28 Jun 2024 11:57:59 GMT

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..

Re: Logic / Dax require

lbendlin — Sun, 30 Jun 2024 14:36:39 GMT

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

Re: Logic / Dax require

PowerBIBeginer — Mon, 01 Jul 2024 05:51:05 GMT

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

Re: Logic / Dax require

Anonymous — Mon, 01 Jul 2024 07:59:43 GMT

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.

Re: Logic / Dax require

PowerBIBeginer — Mon, 01 Jul 2024 08:29:46 GMT

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.

Re: Logic / Dax require

PowerBIBeginer — Wed, 03 Jul 2024 11:35:28 GMT

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

Re: Logic / Dax require

lbendlin — Wed, 03 Jul 2024 11:57:33 GMT

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.

Re: Logic / Dax require

PowerBIBeginer — Fri, 05 Jul 2024 09:44:59 GMT

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.

Re: Logic / Dax require

Anonymous — Mon, 08 Jul 2024 01:28:46 GMT

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