Forum Discussion
Get single value using VAR from multiple value options
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
Hi Anonymous ,
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.
4 Replies
Hi Anonymous ,
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.
Hi Anonymous
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...
