Forum Discussion

Namdu_PAU's avatar
Namdu_PAU
Frequent Visitor
1 year ago
Solved

Counting and Filtering Between Multiple Tables

Hey PBI Community,   Intermediate user of PBI here and require some help once again. I have been slowly digging away at creating this dashboard and I have realised that I have not been filtering m...
Dax Help
  • Namdu_PAU's avatar
    Namdu_PAU
    1 year ago

    Please accept my apologies Poojara! Then you are way too professional in your approach and managed to convinced me it was automated.

    Sincerely, your time and help is much appreciated.

    I was playing around with the filters and I believe I may have encountered something that has worked - but I do not really understand why.

    The thing I changed was the FILTER for the table input for SUMMARIZE. I also changed the table from 'training history' to 'allocated users'.

    # Allocated Users Completed = 

COUNTROWS(
    FILTER(
        SUMMARIZE(
            FILTER('Allocated Users','Allocated Users'[Make] IN DISTINCT(Curriculum[Make]))
            ,USERS[User - User ID]
            ,"Completed", CALCULATE(COUNTA('Training History'[User ID]),FILTER('Allocated Users','Allocated Users'[Make] IN DISTINCT(Curriculum[Make])))
            ,"Total # Courses", [Total Courses]
            ,"Percentage", DIVIDE(COUNTA('Training History'[User ID]),[Total Courses])
            )
        , [Percentage]=1
    )
) + 0

     

    I'll await your response and see if you can validate the solution.

Namdu_PAU's avatar
Namdu_PAU
Frequent Visitor
1 year ago

Hi Poojara,

 

Thank you so much for replying to my request!

 

May I kindly request some additional information on your proposed solutions? I just tried putting the code in for both suggestions and they do not appear to work.

 

There are few things which I think may be stopping it from working:

  1. There is no "Make" column in the 'Training History' table.
  2. Cannot equate 'Training History' to 'Allocated Users' as they are not related
  3. There is no "Percentage Completion" measure or column

 


Also, I do not mean to be rude, but was this solution produced in Chat GPT? The response is structured eerily like an AI model and the code is reflective of it as well.

Poojara_D12's avatar
Poojara_D12
Icon for Super User rankSuper User
1 year ago

Hi Namdu_PAU 

Thank you so much for the genuine feedback, it's not a AI response, as I didn't opened the PBIX file, I just saw the screenshot, also, I was working on a task as well so I guess I got mixed up.

Please allow me some time, will check all the attachments and will get back you you.

 

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

  • Namdu_PAU's avatar
    Namdu_PAU
    Frequent Visitor
    1 year ago

    Please accept my apologies Poojara! Then you are way too professional in your approach and managed to convinced me it was automated.

    Sincerely, your time and help is much appreciated.

    I was playing around with the filters and I believe I may have encountered something that has worked - but I do not really understand why.

    The thing I changed was the FILTER for the table input for SUMMARIZE. I also changed the table from 'training history' to 'allocated users'.

    # Allocated Users Completed = 

COUNTROWS(
    FILTER(
        SUMMARIZE(
            FILTER('Allocated Users','Allocated Users'[Make] IN DISTINCT(Curriculum[Make]))
            ,USERS[User - User ID]
            ,"Completed", CALCULATE(COUNTA('Training History'[User ID]),FILTER('Allocated Users','Allocated Users'[Make] IN DISTINCT(Curriculum[Make])))
            ,"Total # Courses", [Total Courses]
            ,"Percentage", DIVIDE(COUNTA('Training History'[User ID]),[Total Courses])
            )
        , [Percentage]=1
    )
) + 0

     

    I'll await your response and see if you can validate the solution.