Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 |
Need Help
Tips and Tricks
  • v-sshirivolu's avatar
    v-sshirivolu
    1 year ago

    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
        __Result

     

    I'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's avatar
    ajaybabuinturi
    Super User
    1 year ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I want to replace null values from channed to SMS, WA and EMAIL in front for UEF in code

       

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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
  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    1 year ago

    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
        __Result

     

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

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      1 year ago

      Hi Anonymous ,

      Just wanted to check if you had the opportunity to review the suggestion provided?

      If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

       

      Thank you.

       

      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        1 year ago

        Hi Anonymous ,
        Just wanted to check if you had the opportunity to review the suggestion provided? If so, please mark the helpful reply and Accept it as the solution . This will be helpful for other community members who have similar problems to solve it faster.