Tricky Lookup Calculation with Two columns

[helen_brunyee95]

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 🙂

[v-yaningy-msft]

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]

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

Hi @Greg_Deckler 

Thanks for getting back to me.

Here is the V_Ref_Conversion table as text:

Unique_conversion_reference	OLD REF COFDE	NEW REF CODE	Date converted	Site name
C00000020	UL05983	UL10045	04/11/2022	Site A
C00000019	UL04723	UL10072	06/01/2023	Site B
C00000002	UL10013	UL10194	03/04/2023	Site C
C00000001	UL07909	UL10198	10/04/2023	Site D
C00000012	UL08225	UL10245	07/06/2023	Site E
C00000011	UL01148	UL10260	20/06/2023	Site F
C00000006	UL03562	UL10285	08/07/2023	Site G

Here is the D_Sessions Table example as text:

REF CODE	Status	Session Date	Charge
UL05983	Issued	10/07/2018	100
UL05983	Issued	19/07/2018	100
UL10045	Issued	19/07/2018	100
UL04723	Issued	19/07/2018	100
UL10072	Issued	19/07/2018	100
UL10072	Issued	19/07/2018	100
UL07909	Issued	19/07/2018	100
UL01264	Cancelled	21/11/2019	100
UL10194	Issued	30/07/2022	100
UL01241	Complete	11/01/2023	100
UL01148	Issued	15/01/2018	100
UL10285	Issued	19/10/2018	100

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 Month	Jan 23	Feb 23	Mar 23	April 23	May 23	June 23	Jul 23	and so on
a		15	17	22	23	55	56	57
	ULxxxx1	15	17	22	23	5	5	5
	ULxx101					50	51	52
b		20	21	28	21	74	68	52
	ULxxxx2	20	21	28	21	4	3	2
	ULxx101					70	65	50
C		23	17	22	23	37	37	42
	ULxxxx3	23	17	22	23	2	2	2
	ULxx101					35	35	40
d		20	21	28	20	48	46	43
	ULxxxx4	20	21	28	20	1	1	1
	ULxx101					47	45	42

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