Forum Discussion

helen_brunyee95's avatar
helen_brunyee95
Advocate I
2 years ago

Tricky Lookup Calculation with Two columns

Hi All,

 

I have a tricky reporting requirement, I can't quite get my head around how best to do it.

 

I have 2 tables:

1) V_Ref Conversions:

This is a list of old reference codes with their new reference codes (and date of conversion):

 

 

2) D_Sessions

This is a list of all sessions against these ref codes:

 

 

As you can see, these link up on reference code, but what I need to work out is total sessions against both their old ref code and their new ref code. This would just be a count of rows in the D_Sessions table.

The problem is relationships I think, because I can't link the two tables using both old & new ref code columns.

 

Does anyone know how I would go about calculating this? Do I need to restructure the V_Ref conversions table or maybe a lookup table? I have tried various methods but can't get my head around the logic!

 

Any help much appreciated!

 

Thank you 🙂

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    helen_brunyee95 Can you post those tables as text? Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • helen_brunyee95's avatar
      helen_brunyee95
      Advocate I
      2 years ago

      Hi Greg_Deckler 

       

      Thanks for getting back to me.

       

      Here is the V_Ref_Conversion table as text:

      Unique_conversion_referenceOLD REF COFDENEW REF CODEDate convertedSite name
      C00000020UL05983UL1004504/11/2022Site A
      C00000019UL04723UL1007206/01/2023Site B
      C00000002UL10013UL1019403/04/2023Site C
      C00000001UL07909UL1019810/04/2023Site D
      C00000012UL08225UL1024507/06/2023Site E
      C00000011UL01148UL1026020/06/2023Site F
      C00000006UL03562UL1028508/07/2023Site G

       

       

      Here is the D_Sessions Table example as text:

      REF CODEStatusSession DateCharge
      UL05983Issued10/07/2018100
      UL05983Issued19/07/2018100
      UL10045Issued19/07/2018100
      UL04723Issued19/07/2018100
      UL10072Issued19/07/2018100
      UL10072Issued19/07/2018100
      UL07909Issued19/07/2018100
      UL01264Cancelled21/11/2019100
      UL10194Issued30/07/2022100
      UL01241Complete11/01/2023100
      UL01148Issued15/01/2018100
      UL10285Issued19/10/2018100

       

       

      They are linked by REF CODE but I can't link by both new and old to the ref code in sessions...

       

      I am trying to get an output that shows, each month, how many sessions were logged against both the old ref code and the new ref code (see sample layout below). Ie. we are trying to show if sessions increased at a place one it's ref code changed:

       Session MonthJan 23Feb 23Mar 23April 23May 23June 23Jul 23and so on
      a 15172223555657 
       ULxxxx115172223555 
       ULxx101    505152 
                
      b 20212821746852 
       ULxxxx220212821432 
       ULxx101    706550 
                
                
      C 23172223373742 
       ULxxxx323172223222 
       ULxx101    353540 
                
                
      d 20212820484643 
       ULxxxx420212820111 
       ULxx101    474542 
                

       

       

      The issue I am having is, pulling the sessions using both  the old and new ref codes (as they are in two seperate columns), and displaying it so that they appear together if that makes sense?

       

       

      Hope this makes things clearer!

       

      Thanks, Helen

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, helen_brunyee95 

    According to your description, if you just want to get the total sessions(count of rows in the D_Sessions table), you can use the DISTINCTCOUNT, DISTINCTCOUNTNOBLANK functions, etc. If these two functions can't solve your problem, do you consider the DISTINCTCOUNTNOBLANK function?
    Related links:
    DISTINCTCOUNTNOBLANK - DAX Guide
    DISTINCTCOUNT - DAX Guide

    If these two functions do not solve your problem, do you consider:
    1. Connect the OLD REF CODE or NEW REF CODE of the V_Ref table with the REF CODE of the D_Sessions table, and use the USERELATIONSHIP function to create a virtual relationship when using another relationship.
    Related link:
    USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

    2. copy the V_Ref Conversions table (the SUMMARIZE function creates the table) and create a relationship between the OLD REF CODE of the V_Ref 1 table and the NEW REF CODE of the V_Ref 2 table with the D_Sessions table


    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum