Get single value using VAR from multiple value options

[jeongkim]

Hi

Can anyone help me write a full code?

Step1. Try to get value from below data source first.

Table name: GR - 5G SvC_master ~ 2022 - Previous Backup Data

Column name for value to fetch: GR Date

Column name for key identifier to match : Site

Step2. If Step 1 doesn't find any value, then try to get single value from below table which has multiple values so need to get only 1 latest value using Index column(latest= Index bigger number). 

Table name: GR - 5G SvC_master since 2023 - Current Monthly Data

Column name for value to fetch: GR Date

Column name for key identifier to match : Site

Final value destination is below:

(example of code but now error due to my poor coding) 

Table name: Distinct - Master Data

Column name for value destination: GR Date

Column name for key identifier to match: Internal Site ID

[v-tangjie-msft · Accepted solution]

Hi @jeongkim ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create a calculated column.

GR Date =
VAR _a =
    LOOKUPVALUE (
        'GR - 5G SvC_master ~ 2022 - Previous Backup Data'[GR Date],
        'GR - 5G SvC_master ~ 2022 - Previous Backup Data'[Site], [Internal Site ID]
    )
VAR _max_index =
    CALCULATE (
        MAX ( 'GR - 5G SvC_master since 2023 - Current Monthly Data'[Index] ),
        FILTER (
            'GR - 5G SvC_master since 2023 - Current Monthly Data',
            [Site] = EARLIER ( 'Distinct - Master Data'[Internal Site ID] )
        )
    )
VAR _b =
    CALCULATE (
        MAX ( 'GR - 5G SvC_master since 2023 - Current Monthly Data'[GR Date] ),
        FILTER (
            'GR - 5G SvC_master since 2023 - Current Monthly Data',
            [Site] = EARLIER ( 'Distinct - Master Data'[Internal Site ID] )
                && [Index] = _max_index
        )
    )
RETURN
    IF ( ISBLANK ( _a ), _b, _a )

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[jeongkim]

Thank you, it immediately works! 

Thumb up and accepted. 

[Ritaf1983]

Hi @jeongkim 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[jeongkim]

I wrote the full data details and these details seem enough to write the 1 dax code, pls delete this reply so that others would reply and hope you can help other's query, not my query as I remember you that the last time replied similar.