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

[sonuojha1]

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?

Model

Sample file to download:

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

Regards,

Sonu

[amitchandak]

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

Can you share expected output

[sonuojha1]

Hi Amit,

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

Expected output

[Anonymous · Accepted solution]

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

Best Regards