cancel
Showing results 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

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

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 🙂

3 REPLIES 3
Community Support

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?
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.
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!

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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

Announcements

#### 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 Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors