Forum Discussion
replace null values with distinct values from channel using DAX only
| INPUT- |
| | channel | code |
| |----------|----------| |
| | SMS | Sent | |
| | SMS | Del | |
| | WA | Sent | |
| | WA | Del | |
| | Email | Sent | |
| | Email | Del | |
| |(null) | UEF | |
| OUTPUT- |
| | channel | code |
| |----------|----------| |
| | SMS | Sent | |
| | SMS | Del | |
| | WA | Sent | |
| | WA | Del | |
| | Email | Sent | |
| | Email | Del | |
| |SMS | UEF | |
| |WA | UEF | |
| |Email | UEF | |
Hi Anonymous ,
Thanks for reaching out to the Microsoft fabric community forum.
I’ve gone ahead and reproduced your scenario in Power BI, and I’m glad to confirm that the output matches exactly as per your expected result. The logic successfully replaces the null values in the channel column by generating new rows with all distinct non-null channel values for the given code.For your reference, below is the DAX I used to create the final output table:
daxCopyEditFinalTable =
VAR __channels =
FILTER( DISTINCT( 'Table'[channel] ), [channel] <> BLANK() )
VAR __nullCodes =
DISTINCT( SELECTCOLUMNS( FILTER( 'Table', [channel] = BLANK() ), "code", [code] ) )
VAR __BaseTable =
FILTER( 'Table', [channel] <> BLANK() )
VAR __AddTable =
SELECTCOLUMNS(
GENERATE( __channels, __nullCodes ),
"channel", [channel],
"code", [code]
)
VAR __Result =
UNION( __BaseTable, __AddTable )
RETURN
__ResultI've also attached the .pbix file for your reference so you can check and test the setup directly in your environment.
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it
Best Regards,
Sreeteja
Community Support Team.
7 Replies
- ajaybabuinturi
Super User
Hi Anonymous,
Could you please provide more details about your request so that it's easy to understand. The provided deatails are not enough to address.
- Greg_Deckler
Community Champion
Anonymous Maybe:
Table1 = VAR __Channels = FILTER( DISTINCT( 'Table'[channel] ), [channel] <> BLANK() ) VAR __nullCodes = DISTINCT( SELECTCOLUMNS( FILTER( 'Table', [channel] = BLANK() ), "code", [code] ) ) VAR __BaseTable = FILTER( 'Table', [channel] <> BLANK() ) VAR __AddTable = SELECTCOLUMNS( GENERATE( __Channels, __nullCodes ), "channel", [channel], "code", [code] ) VAR __Result = UNION( __BaseTable, __AddTable ) RETURN __Result Hi Anonymous ,
Thanks for reaching out to the Microsoft fabric community forum.
I’ve gone ahead and reproduced your scenario in Power BI, and I’m glad to confirm that the output matches exactly as per your expected result. The logic successfully replaces the null values in the channel column by generating new rows with all distinct non-null channel values for the given code.For your reference, below is the DAX I used to create the final output table:
daxCopyEditFinalTable =
VAR __channels =
FILTER( DISTINCT( 'Table'[channel] ), [channel] <> BLANK() )
VAR __nullCodes =
DISTINCT( SELECTCOLUMNS( FILTER( 'Table', [channel] = BLANK() ), "code", [code] ) )
VAR __BaseTable =
FILTER( 'Table', [channel] <> BLANK() )
VAR __AddTable =
SELECTCOLUMNS(
GENERATE( __channels, __nullCodes ),
"channel", [channel],
"code", [code]
)
VAR __Result =
UNION( __BaseTable, __AddTable )
RETURN
__ResultI've also attached the .pbix file for your reference so you can check and test the setup directly in your environment.
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it
Best Regards,
Sreeteja
Community Support Team.
