Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
helen_brunyee95
Frequent Visitor

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):

helen_brunyee95_1-1711376459504.png

 

 

2) D_Sessions

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

helen_brunyee95_2-1711376603978.png

 

 

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 🙂

3 REPLIES 3
v-yaningy-msft
Community Support
Community Support

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

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.