Field values from 2nd table is not fetching the exact values.

[sherifff]

Hello Experts,

I have created a new PBI dashboard data coming from 2 SQL Tables. I have created a merge query where it will have the data with the selected fields from both the tables. In this 3rd Query Table I want to the date field to be mapped and looked up from the 2nd table where it will have unique entries (by combining 2 fields) . I want to get the related columns from the matching records.

Here are little more details about my current scenario

Table 1 - SQL Table (Transaction Table) - It has all the transactions (LabTest) data
Table 2 - SQL Table (Master Table)- It has the Master Data where it has the results expected range data (Minimum Range Value, Maximum Range Value)
Table 3 - Query Table (Results Table)- I have created a query table by using the Outer merge using the Key Field called "Test Name".

I have a scenario where there are about 10 to 20 columns data to be fetched from the 2nd table (Master Table) into 1st Table (Transaction Table)
The criteria is , for a given record In the transaction table for selected Column "Test Name" ('s field value) it should look for "LabTestTaken Date" Field , Need to check in the Master table if the "Test Name" and the "LabTestTaken Date" fall under/within the date range on the fields of Table 2 (Master Table) called "Start Date" and "End Date" Field.

If "yes" then i need to bring the values of the 10 to 20 fields from that Master table from the matched record.

Note: in the Master Table (Table 2) each "Test Name" is having multiple rows, for example

Test Name                  Start Date           End Date         BP_Min   BP_Maximum
Test 100                     1/1/2000              12/31/2010        70      150
TEST 100                   1/1/2011             06/25/2018          80     170
TEST 100                  06/26/2018          04/09/2020         90      180
TEST 100                  04/10/2020           null (means currently active)

(If you notice the TEST 100 is having 4 entries it is purely based on 4 unique Start and End Date ranges)

The expected results on the transaction table is as follows

Test Name        LabTestTaken Date           BP Actual    BP_Min     BP_Maximum
Test 100              10/10/2008                     125               70               150
TEST 100            12/15/2009                      120               70              150
Test 100              05/10/2015                     140                80              170
Test 100              10/10/2020                     120                90              180

If you notice from the above transaction table the first 2 rows are falling under the first date range which has the BP_Minimum as 70 and BP_Maximum as 150

But currently I am getting all the random values , it just goes and fetches the values from row 1 thorugh 4 and provided the wrong values.

I am sure, there is something on the relationship i need to take care of which i am missing. I hope someone can help me in this one.

[sherifff]

Hi @v-yanjiang-msft ,  Thanks for your reply.  My data source is from SQL table. Could you please guide me how i can do it with SQl table.

Thanks.

[sherifff]

Hi @v-yanjiang-msft ,

I have accepted the solution,  In my Max data i have few more combinations, which if i explain will go more complex hence i haven't mentioned earlier, i will revisit the code and if required will reachout to you.     The other issue since it is realted I feel it will be easy for you to give your sugestions. 

There is a one more column called "Lab Location"  , there is a need to show the total of "Test100" grouped by "Lab Location" ,  The main point here is, all the  BP_Min , BP_Max, etc., all are of Data type "String".  As for some of the tests , they do mark it as "Not applicable", "Special Conditions" unfortnately in these fields.   So i cannot convert them directly into numbers. 
Is that possible to calculate without making over burden on the tables, by using DAX.

Example

Test Name      Lab Location     Lab Test taken Date Actual   BP_Min        Actual_Group Total  BP_Group total

Test100          EAST                  1/1/2000       90.      70     185.   150

Test100          EAST          6/30/2019.    95.      80        185.           150

Test100          North                1/1/2000        110.    100       240.     220

Test100          North     06/25/2016.  130.     120                  240.     220

for East Group the total comes to 150 

And for North - 220

for BP_Group Total, for East Group the total comes to 150 

And for North - 220

and for Actual_Group total

for East Group the total comes to 185

And for North - 240

Could you plz help me in this calculations.

[v-yanjiang-msft]

Hi @sherifff ，

You can use the VALUE function in the DAX, like this:

BP Group total =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Lab location] = MAX ( 'Table'[Lab location] )
    ),
    VALUE ( 'Table'[BP_Min] )
)

Actual Group total =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Lab location] = MAX ( 'Table'[Lab location] )
    ),
    VALUE ( 'Table'[Actual] )
)

Get the expected result.

Refer to the VALUE function.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

[sherifff]

Hi @v-yanjiang-msft,

Thanks for sharing the formula, but in the requirment as i have mentioned the Actual and Bp_Min columns have text values in it, which i don't see in your file.   It is how it looks at my end on the table.

Due to the text values i am getting the below error.

How do we calculate just the number values , filtering out and ignoring any text values in the column and the rest of the condition is same as mentioned in the earlier post?

[sherifff]

Hi @v-yanjiang-msft,

I hope you got my reply to your earlier post. could you please provide updated formula?

Thanks

[sherifff]

Hi @v-yanjiang-msft , i have updated the post corrected the question 

[sherifff]

Hi @v-yanjiang-msft,

Thanks for providing the updated code, i tested it out it works great.  I have one more situation which is tied to this issue, where there is a special tests which has spl start and end dates.   The values for the Min and Max should show based on this (comparing these) special dates.  

Could you please help me with senario as well, I really appreciate your help.

Below screen would give details

[sherifff]

@v-yanjiang-msft @Could you please provide your thoughts

Thanks

[sherifff]

Hi @v-yanjiang-msft 

Please refer the attached screen shots.   If you look at the transaction table., thats the final output  i am looking at.

Screen 1: The master table.   Here the scenario is it has

1) Same "TestName" but with different start and end dates

2) The "Min" and "Max" for [BP], [Sugar] and [Cholesterol] have values which is again might be different based on Start and End dates  (some will have same Min and Max values even with different start and end dates) 

The Below screen is of the Transaction Table.  

Here i need to bring the Minimum and Maximum values from Master table.

The condition it has to match is as follows

The criteria is , for a given record In the transaction table for "Test Name" ('s field value) it should look for "LabTestTaken Date" Field , Need to check in the Master table if the "Test Name" and the "LabTestTaken Date" fall under/within the date range on the fields of Table 2 (Master Table) called "Start Date" and "End Date" Field.

The part i missed was the "Test Type" is another criteria,  which it should bring the values of BP Min and Max values if it is for BP as the "Test Type" ,  and same way it should bring for other values (Sugar_Min, Sugar_Max , Cholesterol_Min  and Cholesterol_Max) all these values should be displayed under Minimum and Maximum columns (Refer column "D"  and Column "E"

I hope i have explained it what i am looking for.  Please let me know if you need any futher information.

Thank you so much!

[v-yanjiang-msft]

Hi @sherifff ,

According to your description, if you use merge query like bellow, as in the merge, it will only correspond to matching columns, and will not calculate whether LabTestTaken Date is between Start Date and End Date, so the result is that all time periods in the Master Table will appear in the same LabTestTaken Date.

My solution is to achieve through DAX function.

1.Create a new table with all columns in Master Table you want appearing in the result, I add another test name in the Master Table called TEST200.

Table =
SUMMARIZE (
    'Master Table',
    'Master Table'[Test Name],
    'Master Table'[BP_Min],
    'Master Table'[BP_Max],
    'Master Table'[Start Date],
    'Master Table'[End Date]
)

2.Make relationship between the new table and Transaction Table.

3.Create a measure in Transaction Table.

BP_Actual =
IF (
    MAX ( 'Transaction Table'[Test Name] ) = MAX ( 'Table'[Test Name] )
        && MAX ( 'Transaction Table'[LabTestTaken Date] ) > MAX ( 'Table'[Start Date] )
        && MAX ( 'Transaction Table'[LabTestTaken Date] ) < MAX ( 'Table'[End Date] ),
    MAX ( 'Transaction Table'[BP Actual] ),
    BLANK ()
)

4.Get the expected result by putting these elements into the visual like me.

5.In your sample, the LabTestTaken Date in the 4th row of result table correspondence to the fourth time period of the Master table which has no  value of BP_Min and BP_Max, so there is no such one in my result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[sherifff]

Hi @v-yanjiang-msft  , Thank you very much for taking time and going over my issue and providing the detailed steps and the file. 
I have 2 more senarios associted with my issue, which i forgot to mention in my initial request. 
1) The Transaction Table has the "LabTestTaken Date" also has duplicate values and also the BP Actual Value also has duplicate values  (Please refer the below table).

Test Name	LabTestTaken Date	BP Actual
TEST100	10/10/2008	125
TEST100	10/10/2008	126
TEST100	10/10/2008	125
TEST100	12/15/2009	121
TEST100	12/15/2009	121
TEST100	12/15/2009	123
TEST100	5/10/2015	141
TEST100	5/10/2015	142
TEST100	5/10/2015	141
TEST100	10/10/2020	130
TEST100	10/10/2020	131
TEST200	10/10/2008	131
TEST200	10/10/2008	125
TEST200	10/10/2008	126
TEST200	10/10/2008	126

2) In the Transaction Table there are other columns which will have actual values like for the tests "Sugar" , "Cholesterol".  The requirement is to have one "Actual Value" Column  and based on the Test Type the actual value should display. Similarly for the "Min" and "Max" Columns as well, should display the values based on the Test Type and it should also staisfy the dates range.

Test Name  LabTestTakenDate Actual  Min  Max  Test Type

TEST100	10/10/2008 0:00	125	70	150	BP
TEST100	10/10/2008 0:00	125	70	150	BP
TEST200	10/10/2008 0:00	125	70	150	BP
TEST200	10/10/2008 0:00	125	70	150	BP
TEST100	12/15/2009 0:00	120	70	150	BP
TEST100	5/10/2015 0:00	140	80	170	BP
TEST100	10/10/2008 0:00	250	100	500	Cholesterol
TEST100	10/10/2008 0:00	255	100	500	Cholesterol
TEST200	10/10/2008 0:00	250	100	500	Cholesterol
TEST100	12/15/2009 0:00	300	100	500	Cholesterol
TEST100	12/15/2009 0:00	305	100	500	Cholesterol
TEST100	5/10/2015 0:00	400	200	400	Cholesterol
TEST200	10/10/2008 0:00	110	150	250	Sugar
TEST100	12/15/2009 0:00	180	150	250	Sugar
TEST200	10/10/2008 0:00	110	150	250	Sugar
TEST100	12/15/2009 0:00	180	150	250	Sugar
TEST100	5/10/2015 0:00	105	125	175	Sugar

Could you please provide some insights on how this can be acheived.   Thanks a lot for your help.

[v-yanjiang-msft]

Hi @sherifff ,

I'm sorry for that I'm not very clear about your senarios 2, and what is your finnal expected result.

Best Regards,
Community Support Team _ kalyj

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