topic DAX formula IF measure returns null value then search ID and match with another table ID in DAX Commands and Tips

DAX formula IF measure returns null value then search ID and match with another table ID

raj302016 — Tue, 27 Aug 2024 13:41:17 GMT

I need Expert support on below query.

I've made below table using existing datasets and measures, which gives me SW_PART value against each of opportunity using multiple criteria.
Now, I want next level DAX calculation to merge/show single row which is first row, the calculation should give me second row result into First row, I mean value 5720 (under OPP-10225) -should be visible/copy to OPP-10236 and later I will hide the OPP-10225 from the table visual.

SW_PART is already a measure

Opportunity ID	Opportunity Name	End Customer Name	SW_PART
OPP-10236	VD OPS_Support	Apple
OPP-10225	VD OPS	Apple	5720
OPP-10237	MD OPS_Support	MS
OPP-10226	MD OPS	MS	8890
OPP-10238	DD OPS_Support	Virgin
OPP-10227	DD OPS	Virgin	23345

To identify the correct opportunity ID, I've made below excel table, which I've already imported to PowerBI.
This table help to identify the which NULL Opportunity ID from above table matches Original OPP_ID to this table then search Product OPP_ID = Opportunity ID and get the value or calculate value then return the value against Opportunity ID

Original Opp_ID	Product OPP_ID	Product Quote	_PK Quote ID
OPP-10236	OPP-10225	TST-024693	7D1E2089-3CF9-ED11-8F6E-6045BD11F57A
OPP-10108	OPP-10425	TST-025442	BFB78603-F7B8-ED11-B596-00224801E567

I've used below DAX but it's showing same values in against all NULL opportunities

SW_PART_Filled =

VAR ValuefromOPP_ID =

CALCULATE(

[SW_PART],

FILTER(

ALL('Quote Details - All Records'),

'Quote Details - All Records'[_FK Quote ID] = MAX('VN-OPP Identifier'[_PK Quote ID])

)

RETURN

IF(

ISBLANK([SW_PART]),

ValuefromOPP_ID,

[SW_PART]

)

Results I am getting as below, which showing same results against opportunity

Opportunity ID	Opportunity Name	End Customer Name	SW_PART
OPP-10236	VD OPS_Support	Apple	5720
OPP-10225	VD OPS	Apple	5720
OPP-10237	MD OPS_Support	MS	5720
OPP-10226	MD OPS	MS	8890
OPP-10238	DD OPS_Support	Virgin	5720
OPP-10227	DD OPS	Virgin	23345

Re: DAX formula IF measure returns null value then search ID and match with another table ID

xifeng_L — Tue, 27 Aug 2024 16:18:37 GMT

Hi raj302016

You can refer to below measure.

SW_PART_Filled = VAR ProdOppID = LOOKUPVALUE('VN-OPP Identifier'[Product OPP_ID],'VN-OPP Identifier'[Original Opp_ID],MAX('Quote Details'[Opportunity ID])) VAR TempTbl = CALCULATETABLE( ADDCOLUMNS( SUMMARIZE( 'Quote Details', 'Quote Details'[Opportunity ID], 'Quote Details'[Opportunity Name], 'Quote Details'[End Customer Name] ), "SW_PART", [SW_PART_Measure] ), ALLSELECTED() ) RETURN MAXX( FILTER(TempTbl,'Quote Details'[Opportunity ID]=ProdOppID), [SW_PART] )

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: DAX formula IF measure returns null value then search ID and match with another table ID

raj302016 — Wed, 28 Aug 2024 04:15:20 GMT

xifeng_L - Thank you for the solution.

The DAX is working fine but when I trying to hide this OPP-10225 the calculation also removed from OPP-10236 as highlighted below. I don't want to show duplicate figures on row lables and Grand total also.

As I mentioned in my initial thread that once DAX calculates the figures and show against OPP.... then I will hide the previous OPP from the table visual but calculated figures remains there.

Thanks,

Re: DAX formula IF measure returns null value then search ID and match with another table ID

xifeng_L — Wed, 28 Aug 2024 14:59:57 GMT

I've made some changes. You can try again.

SW_PART_Filled = SUMX( VALUES('Quote Details'[Opportunity ID]), VAR ProdOppID = LOOKUPVALUE('VN-OPP Identifier'[Product OPP_ID],'VN-OPP Identifier'[Original Opp_ID],'Quote Details'[Opportunity ID]) RETURN CALCULATE( [SW_PART_Measure], 'Quote Details'[Opportunity ID]=ProdOppID, ALL('Quote Details'[Opportunity Name],'Quote Details'[End Customer Name]) ) )