Forum Discussion

sonuojha1's avatar
sonuojha1
Helper IV
5 years ago
Solved

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?

 

 

Model

 

 

 

Sample file to download:

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

 

Regards,

Sonu

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

3 Replies

    • sonuojha1's avatar
      sonuojha1
      Helper IV
      5 years ago

      Hi Amit,

       

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

      Expected output

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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