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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sonuojha1
Helper IV
Helper IV

How to get data from Multiple Transaction (Fact ) tables!!

Hi All,

 

I need your expert advise on the requirement to fullfill . Could you please suggest how to achieve it.

 

I have a below requirement to get data from two transaction tables. Below is requirement, Data model and sample PBIX file attached.

Requirement: 

I need to fetch data from two transaction tables:

1. Approved Courses- This is factless fact table, it contains Employee FK, Course FK. All courses are in this table are approved.

2. Completed Courses- This table contains Completed course information with Employee FK, course FK and complete %.

I created two Dimensions:

1. Employee Dimension

2. Course Dimension

 

Question1.: How to achieve below expected output?

Question2: How to get count of courses which completed? So that I can see Total Assigned(Approved) Courses and Total completed Courses all together in same table?

 

sonuojha1_0-1622017760731.png

 

Model

sonuojha1_0-1622018809205.png

 

 

 

Sample file to download:

https://1drv.ms/u/s!Al5NrA4zIB7OnEimWwJi34joUlDH?e=61B0Ph

 

Regards,

Sonu

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @sonuojha1 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Delete the relationship base on employee number among Dim - Employee, Fact - Approved Course and Fact - Course Completed table

2. Change the data type of employee number and course id with same data type among four tables

3. Create a measure to judge whether the current course is approved or completed one

Flag = 
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Fact - Approved Course'[CourseID] ),
        FILTER (
            'Fact - Approved Course',
            'Fact - Approved Course'[Employee Number]
                = SELECTEDVALUE ( 'Dim - Employee'[Employee Number] )
        )
    )
VAR _tab2 =
    CALCULATETABLE (
        VALUES ( 'Fact - Course Completed'[CourseID] ),
        FILTER (
            'Fact - Course Completed',
            'Fact - Course Completed'[EmpID]
                = SELECTEDVALUE ( 'Dim - Employee'[Employee Number] )
        )
    )
RETURN
    IF (
        (
            SELECTEDVALUE ( 'Dim - Course'[CourseID] )
                IN _tab
                    || SELECTEDVALUE ( 'Dim - Course'[CourseID] ) IN _tab2
        ),
        1,
        0
    )

4. Create two measures as below to get the approved courses and completed%

Approved Courses = 
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Fact - Approved Course'[CourseID] ),
        FILTER (
            'Fact - Approved Course',
            'Fact - Approved Course'[Employee Number]
                = SELECTEDVALUE ( 'Dim - Employee'[Employee Number] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Dim - Course'[CourseID] ) IN _tab,
        SELECTEDVALUE ( 'Dim - Course'[CourseName] ),
        BLANK ()
    )
Completed% = 
CALCULATE (
    MAX ( 'Fact - Course Completed'[Complete %] ),
    FILTER (
        'Fact - Course Completed' ,
        'Fact - Course Completed'[EmpID]
            = SELECTEDVALUE ( 'Dim - Employee'[Employee Number] )
            && 'Fact - Course Completed'[CourseID] = SELECTEDVALUE ( 'Dim - Course'[CourseID] )
    )
)

yingyinr_0-1622183140208.png

Best Regards

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@sonuojha1 , what measure you need that is not clear?

Can you share expected output

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi Amit,

 

Expected output is the excel image attached here. Need to show approved cases 

Expected output

sonuojha1_0-1622028933262.png

 

Anonymous
Not applicable

Hi @sonuojha1 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Delete the relationship base on employee number among Dim - Employee, Fact - Approved Course and Fact - Course Completed table

2. Change the data type of employee number and course id with same data type among four tables

3. Create a measure to judge whether the current course is approved or completed one

Flag = 
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Fact - Approved Course'[CourseID] ),
        FILTER (
            'Fact - Approved Course',
            'Fact - Approved Course'[Employee Number]
                = SELECTEDVALUE ( 'Dim - Employee'[Employee Number] )
        )
    )
VAR _tab2 =
    CALCULATETABLE (
        VALUES ( 'Fact - Course Completed'[CourseID] ),
        FILTER (
            'Fact - Course Completed',
            'Fact - Course Completed'[EmpID]
                = SELECTEDVALUE ( 'Dim - Employee'[Employee Number] )
        )
    )
RETURN
    IF (
        (
            SELECTEDVALUE ( 'Dim - Course'[CourseID] )
                IN _tab
                    || SELECTEDVALUE ( 'Dim - Course'[CourseID] ) IN _tab2
        ),
        1,
        0
    )

4. Create two measures as below to get the approved courses and completed%

Approved Courses = 
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Fact - Approved Course'[CourseID] ),
        FILTER (
            'Fact - Approved Course',
            'Fact - Approved Course'[Employee Number]
                = SELECTEDVALUE ( 'Dim - Employee'[Employee Number] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Dim - Course'[CourseID] ) IN _tab,
        SELECTEDVALUE ( 'Dim - Course'[CourseName] ),
        BLANK ()
    )
Completed% = 
CALCULATE (
    MAX ( 'Fact - Course Completed'[Complete %] ),
    FILTER (
        'Fact - Course Completed' ,
        'Fact - Course Completed'[EmpID]
            = SELECTEDVALUE ( 'Dim - Employee'[Employee Number] )
            && 'Fact - Course Completed'[CourseID] = SELECTEDVALUE ( 'Dim - Course'[CourseID] )
    )
)

yingyinr_0-1622183140208.png

Best Regards

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors