cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sherifff
Helper II
Helper II

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

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.

53 REPLIES 53
parry2k
Super User
Super User

@sherifff it is done, I will share the solution later today. Cheers!!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k ,

 

Could you please share the solution.

Thanks.

Hi @parry2k,

Awesome. Look forward for your solution.

 

Thanks

parry2k
Super User
Super User

@sherifff I see, it is pretty small then we are good. I'm just need to resolve one last corner case then it should be good to go.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k@parry2k 

Thank you so much for your help. Anxious to see solution.

parry2k
Super User
Super User

@sherifff it is almost done. BTW, how large is your dataset? especially how large is your master table and transaction table in terms of the number of rows, approx. is fine.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k ,

 

The master table is having only 20 records.  And it wont grow anytime soon, if at all then it might be in few numbers 

As for as Transaction Table, it has got roughly around 110 K  records.

 

Thanks

parry2k
Super User
Super User

@sherifff I had the solution until now, you have added another logic to it. **bleep** it 🙂






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k , 

 

Just a thought, If the Spl type with "Spl_Start Date" and "Spl_End Date" are the ones creating complexity/problems ,  if yes, then is  it a good idea to have 2 separate measures one for "normal" ones and another one for "Special" type?

parry2k
Super User
Super User

@sherifff thanks for the explanation, it is helpful.

 

Quick question, which date to use start and end date or SQL start and end date?

 

if the start and end date, how come some of the records in the master table don't have a start date, I understand the end date is blank because that test range is still active?

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k ,

 

The Tests which are of special "Test types" has the numbers in 4 digits here in our case  TEST7000 and TEST8000 are of Special type and for these tests, the start date to be looked into is  "Spl_StartDate" and "Spl_EndDate"

For all other Tests which are of normal types will use the regular "Start Date" and "End Date"  example: TEST100 and TEST200 etc.,

There are 2 fields which i would like to mention is

1) ProcedureCode    and  2) TestType

The field "Test Name" is a conditional column (not calculated column) which comes from the source column called "Procedure Code" which is in the same 'Transaction Table' itself.   There are conditions based on which the procedure code will have the test name as "Test100" v/s "Test200".

Please refer the below screen shot.

sherifff_0-1641488437268.png

and then the next step i do is filter it out the blank values and have only the ones with the values .


2)  The "Test Type" is the field which has the value as whether the test is of "Special" Type  or "Normal" Type.

if it is Special Type then the regular "Start Date" and "End Date" will be empty and the "Spl_Start Date" and "Spl_End Date" will be used. 

 

I hope the below table should explain it better.

 

Test Name   Test Type     Start Date   End Date            Spl_Start Date     Spl_End Date
TEST100        Normal          In Use         In Use                Not-Used                null 
TEST200        Normal          In Use        In Use                 Not-Used                null
TEST7000      Special             null           null                       In Use                  In Use

TEST8000      Special             null           null                       In Use                  In Use

 

Please let me know if you have any questions.

 

Thanks

parry2k
Super User
Super User

@sherifff I'm not going to scroll thru all the messages, just copy and paste them here.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k,

Thanks for your reply.  I am trying to explain the requirement from start.

 

I have created a new PBI dashboard data coming from 2 SQL Tables.

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)


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: 1) The "Test Name" in Transaction Table it is a Conditional Column as this is derived based on conditions from another field called "Test Code"

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

 

Test Name Start Date End Date Spl_StartDate Spl_EndDate BP_Min BP_Maximum
Test 100 1/1/2000 12/31/2010 10/10/2015 null 70 150
TEST 100 1/1/2011 06/25/2018 10/10/2015 null 80 170
TEST 100 06/26/2018 04/09/2020 02/02/2014 null 90 180
TEST 100 04/10/2020 null (means currently active) null 100 200
TEST 200
TEST 200
TEST 7000 null null 1/1/2010 12/15/2018 65 60
TEST 7000 null null 12/15/2018 null 50 75
TEST 8000 null null 2/10/2019 null 110 210

(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.


Here are few important criteria:
1) The criteria for "Lab Test Taken date" which has value before the "start date" field of Master table should get the values from the very first entry of "Start date" . For example , the entry for TEST100 "Lab Test Taken Date" is = 4/15/1997 , should get the BP_Min value from the "Start Date = 1/1/2000 and the value would be "70".

2) if the "Lab Take Taken Date" is lets say today's date (Current date) then it should get the values of Min and Max from the entry which has null "end date". for Example
the entry for TEST100 "Lab Test Taken Date" is = 1/5/2022 , should get the BP_Min value from the "Start Date = 4/10/2020 and the value would be "100".

 

Below is the Master Table data

Test NameStart DateEnd DateBP_MinBP_MaxCholesterol_MinCholesterol_MaxSugar_MinSugar_MaxSql_Start DateSql_End Date
TEST1001/1/2000 0:0012/31/2010 0:00701451005001502501/1/2000 0:00 
TEST1001/1/2011 0:006/25/2018 0:00601401005001402501/1/2011 0:00 
TEST1006/26/2018 0:004/9/2020 0:00901802506501302506/26/2018 0:00 
TEST1004/10/2020 0:00 701352756751602504/10/2020 0:00 
TEST2001/1/2000 0:0012/31/2010 0:00701501005001502501/1/2000 0:00 
TEST2001/1/2011 0:005/20/2021 0:00851552004003004001/1/2011 0:00 
TEST7000  4550110120801401/1/2017 0:008/31/2020 0:00
TEST7000  5055115125851459/1/2020 0:00 
TEST8000  1102102803259015510/1/2019 0:00 
           

 

 

The below is the expected results in the Transaction Data.

Test NameLab Test Taken DateTest TypeActual ValueMinimum ValueMaximum Value
TEST1004/15/1997 0:00BP12070145
TEST10010/10/2008 0:00BP25070145
TEST10010/10/2008 0:00Cholesterol505100500
TEST10012/15/2009 0:00BP12070145
TEST10012/15/2009 0:00Cholesterol300100500
TEST10012/15/2009 0:00Sugar180150250
TEST10012/15/2014 0:00Sugar180140250
TEST1005/10/2015 0:00BP14060140
TEST1005/10/2015 0:00Sugar200140250
TEST1003/15/2019 0:00Cholesterol305250650
TEST10012/10/2021 0:00BP31070135
TEST20010/10/2008 0:00BP25070150
TEST20010/10/2008 0:00Cholesterol250100500
TEST20010/10/2008 0:00Sugar110150250
TEST2005/10/2015 0:00Cholesterol380200400
TEST2005/10/2015 0:00Sugar105300400
TEST70008/2/2016 0:00Sugar22280140
TEST70008/20/2019 0:00BP334550
TEST700010/15/2021 0:00Cholesterol124115125
TEST80004/12/2021 0:00BP123110210
      

 

Please let me know if you need any other information.

 

Thanks.

 

 

parry2k
Super User
Super User

@sherifff I don't think the relationship is going to help here. You need to load these tables as it is and create measures/columns to pull the data from the master table based on the actual lab test date. the measure is better as these get calculated on the fly, not get loaded in the memory.

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k ,

 

Thanks for your reply,  KalyJ the Community support peron, helped me to get around through DAX for these 2 values which i need to get from the master table., but i ended up with the situation which i have explained it in detail in the post Message # 24. 
Is that possible for you to provide solution on this issue.

Thanks

v-kalyj-msft
Community Support
Community Support

Hi @sherifff ,

According to your description, I create a new sample, Min and Max are measures in Transaction table.

Min =
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] )
    )
RETURN
    SWITCH (
        MAX ( 'Transaction'[Test Type] ),
        "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Min] ), _T ),
        "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Min] ), _T ),
        "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Min] ), _T )
    )
Max =
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] )
    )
RETURN
    SWITCH (
        MAX ( 'Transaction'[Test Type] ),
        "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Max] ), _T ),
        "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Max] ), _T ),
        "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Max] ), _T )
    )

 

Get the expected result.

vkalyjmsft_0-1638932949931.png

 

I attach my sample below to help you understanding.

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.

Hi @v-kalyj-msft ,

 

I got into issue with the transaction data (It is not bringing the values) where the transaction date is kind of out of range from the master data table.

 

In the below screen i am trying to explain the issue.  Here in my case the "Lab test taken date" falls either before the "Start Date" or it falls in the "End Date" which is empty , which technically means it is valid for the current dates.
The TEST100 there is no value in the "End Date" means if the Transaction date is lets say today's date it should take the value from the record where the end date is empty. 

sherifff_0-1639099983158.png

 

Is that addiing additional condition to the existing code should get the results , could you please put some insights or updated code in fixing this issue will be highly appreicated.

 

 

Hi @sherifff ,

According to your description, I modify my formula, the date which has no value in the "End Date" will return the correct result, for the date which is out of range from the master data table, what data do you want it return, it is logically correct to return nothing.

Min =
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
RETURN
    IF (
        MAX ( 'Transaction'[Lab Test Taken Date] )
            > MAXX (
                FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                'Master'[Start Date]
            ),
        SWITCH (
            MAX ( 'Transaction'[Test Type] ),
            "BP", MAX ( 'Master'[BP_Min] ),
            "Cholesterol", MAX ( 'Master'[Cholesterol_Min] ),
            "Sugar", MAX ( 'Master'[Sugar_Min] )
        ),
        SWITCH (
            MAX ( 'Transaction'[Test Type] ),
            "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Min] ), _T ),
            "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Min] ), _T ),
            "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Min] ), _T )
        )
    )

 

Max =
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] )
    )
RETURN
IF (
        MAX ( 'Transaction'[Lab Test Taken Date] )
            > MAXX (
                FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                'Master'[Start Date]
            ),
        SWITCH (
            MAX ( 'Transaction'[Test Type] ),
            "BP", MAX ( 'Master'[BP_Max] ),
            "Cholesterol", MAX ( 'Master'[Cholesterol_Max] ),
            "Sugar", MAX ( 'Master'[Sugar_Max] )
        ),
    SWITCH (
        MAX ( 'Transaction'[Test Type] ),
        "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Max] ), _T ),
        "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Max] ), _T ),
        "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Max] ), _T )
    )
)

 

vkalyjmsft_0-1639117759529.png

 

I attach my sample below to help you understanding.

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.

Hi @v-kalyj-msft ,

 

Thanks for sharing the code.  The criteria for Lab Test Taken date which has value  before the "start date" field should get the values from the very first "Start date" for that test.  In this case the entry for  TEST100   Test Taken Date is  = 4/15/1997 ,  should  get the BP_Min value from the "Start Date = 1/1/2000 and the value would be "70". 

Thanks again

 

 

Hi @sherifff ,

According to your description, I modify the formula like this:

Min = 
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && ( 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] ) )
    )
RETURN
    IF (
        MAX ( 'Transaction'[Lab Test Taken Date] )
            > MAXX (
                FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                'Master'[Start Date]
            ),
        SWITCH (
            MAX ( 'Transaction'[Test Type] ),
            "BP", MAX ( 'Master'[BP_Min] ),
            "Cholesterol", MAX ( 'Master'[Cholesterol_Min] ),
            "Sugar", MAX ( 'Master'[Sugar_Min] )
        ),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                < MINX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP", MIN ( 'Master'[BP_Min] ),
                "Cholesterol", MIN ( 'Master'[Cholesterol_Min] ),
                "Sugar", MIN ( 'Master'[Sugar_Min] )
            ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Min] ), _T ),
                "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Min] ), _T ),
                "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Min] ), _T )
            )
        )
    )
Max =
VAR _T =
    FILTER (
        'Master',
        'Master'[Test Name] = MAX ( 'Transaction'[Test Name] )
            && 'Master'[Start Date] < MAX ( 'Transaction'[Lab Test Taken Date] )
            && 'Master'[End Date] > MAX ( 'Transaction'[Lab Test Taken Date] )
    )
RETURN
    IF (
        MAX ( 'Transaction'[Lab Test Taken Date] )
            > MAXX (
                FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                'Master'[Start Date]
            ),
        SWITCH (
            MAX ( 'Transaction'[Test Type] ),
            "BP", MAX ( 'Master'[BP_Max] ),
            "Cholesterol", MAX ( 'Master'[Cholesterol_Max] ),
            "Sugar", MAX ( 'Master'[Sugar_Max] )
        ),
        IF (
            MAX ( 'Transaction'[Lab Test Taken Date] )
                < MINX (
                    FILTER ( 'Master', 'Master'[Test Name] = MAX ( 'Transaction'[Test Name] ) ),
                    'Master'[Start Date]
                ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP", MIN ( 'Master'[BP_Max] ),
                "Cholesterol", MIN ( 'Master'[Cholesterol_Max] ),
                "Sugar", MIN ( 'Master'[Sugar_Max] )
            ),
            SWITCH (
                MAX ( 'Transaction'[Test Type] ),
                "BP", CALCULATE ( SELECTEDVALUE ( 'Master'[BP_Max] ), _T ),
                "Cholesterol", CALCULATE ( MAX ( 'Master'[Cholesterol_Max] ), _T ),
                "Sugar", CALCULATE ( MAX ( 'Master'[Sugar_Max] ), _T )
            )
        )
    )

Get the expected result.

vkalyjmsft_0-1639131917040.png

I attach my sample below for referece.

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.

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!