Non blank value in column

[Anonymous]

Hi Team,

I have base table as below 

Code	Region	Sales
123		10
123	ABC	20
456		30
456	DEF	40

I have created a Distinct value from code as below

Code

123

456

I need to create allokup region column from the base as below.

If the value if blank in region it should look for next value.

Code	Region
123	ABC
456	DEF

Thanks in advance !

[rusgesig · Accepted solution]

Try this:

Region Table =

DISTINCT(

    SELECTCOLUMNS(

        FILTER('Table','Table'[Region]<>BLANK())

        ,"Code", 'Table'[Code]

        ,"Region", 'Table'[Region]

    ))

[FreemanZ]

hi @Anonymous 

try to create a calculated table like this:

Table =
SUMMARIZE(
    BaseTable,
    BaseTable[Code],
    BaseTable[Region]
)

[Anonymous]

@FreemanZ ,

Thanks for your rply !

This is not working as expected attaching the image.

Thanks !

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end
https://dropmefiles.com/eahTZ

[Anonymous]

@Ahmedx ,

I am not able to download the file can you please share it again.

Thanks!

[Ahmedx]

https://dropmefiles.com/r31U6
https://wdfiles.ru/5654048

[jaweher899]

To create the "Allokup Region" column, you can use the DAX formula "CALCULATE" with "IF" and "MIN" functions. Here's the formula:

Allokup Region = CALCULATE(MIN(Base[Region]), FILTER(Base, Base[Code]=DistinctCodes[Code] && NOT(ISBLANK(Base[Region]))))

Assuming your base table is named "Base" and your distinct code table is named "DistinctCodes", you can use this formula in a new calculated column. The formula finds the minimum non-blank value of the "Region" column for each code.

[Anonymous]

@jaweher899 ,

This code is not working as expected plz find below images. 131 code should have value but in lookup table I still see blank.