Forum Discussion

dsubhankar's avatar
dsubhankar
New Member
10 years ago
Solved

Count from related Table

Im new to Power BI. I have two tables, User and Profiles, relationship is alreaddy created. Profiles table has status with option Submitted/Nonsubmittet. I would like to see all Users total count on Submitted and Nonsubmitted profiles. Like

 

User          Submitted             Nonsubmitted

 

A                  10                               3

 

B                 7                                  5

 

I have tried by adding new columns but syntax is not correct. If anyone help me out.

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    10 years ago

    Hi Dsubankar,

     

    I have tested it on my local environment, the steps below is for you reference.

     

    1. Create a calculated column in Profiles table using the DAX below.
      Count = CALCULATE(COUNTA(Profiles[Status]),ALLEXCEPT(Profiles,Profiles[UserID],Profiles[Status]))
    2. Create two calculated columns on User table using the DAX below.
      Submitted = IF(ISBLANK(LOOKUPVALUE(Profiles[Count],Profiles[UserID],User[UserID],Profiles[Status],"Submitted")),0,LOOKUPVALUE(Profiles[Count],Profiles[UserID],User[UserID],Profiles[Status],"Submitted"))
      Nonsubmittet = IF(ISBLANK(LOOKUPVALUE(Profiles[Count],Profiles[UserID],User[UserID],Profiles[Status],"Submitted")),0,LOOKUPVALUE(Profiles[Count],Profiles[UserID],User[UserID],Profiles[Status],"Nonsubmittet"))

     

    Regards,

    Charlie Liao

6 Replies

  • waltheed's avatar
    waltheed
    Impactful Individual
    10 years ago

    Hi dsubhankar,

     

    That should not be too hard. I tried to create your example in Excel. See screenshot. I hope it is correct.

    The left 2 tables are Users and Profiles.

    These are imnported into the PowerPivot Model, with a relationship on User.

    The third table is a pivot table in Excel that is connected to the Power Pivot model.

     

     

      

    The only "formula" that you need is this measure:

    Count of Profiles:=COUNTROWS('Profiles')

     

    • dsubhankar's avatar
      dsubhankar
      New Member
      10 years ago

      Hi,

      Thanks for your response but Im using Power BI Desktop and connecting to MySQL DB. Can you let me know

      • waltheed's avatar
        waltheed
        Impactful Individual
        10 years ago

        It should be exaclty the same with Power BI Desktop and MySQL as datasource.

        The model and the formula are identical.

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    10 years ago

    Hi Dsubankar,

     

    I have tested it on my local environment, the steps below is for you reference.

     

    1. Create a calculated column in Profiles table using the DAX below.
      Count = CALCULATE(COUNTA(Profiles[Status]),ALLEXCEPT(Profiles,Profiles[UserID],Profiles[Status]))
    2. Create two calculated columns on User table using the DAX below.
      Submitted = IF(ISBLANK(LOOKUPVALUE(Profiles[Count],Profiles[UserID],User[UserID],Profiles[Status],"Submitted")),0,LOOKUPVALUE(Profiles[Count],Profiles[UserID],User[UserID],Profiles[Status],"Submitted"))
      Nonsubmittet = IF(ISBLANK(LOOKUPVALUE(Profiles[Count],Profiles[UserID],User[UserID],Profiles[Status],"Submitted")),0,LOOKUPVALUE(Profiles[Count],Profiles[UserID],User[UserID],Profiles[Status],"Nonsubmittet"))

     

    Regards,

    Charlie Liao

    • dsubhankar's avatar
      dsubhankar
      New Member
      10 years ago

      Hey Charlie,

       

      Thanks. Its works. Can you please tell me how we can add filter in Count Column.

      Like here:

       

      Count = CALCULATE(COUNTA(Profiles[Status]),ALLEXCEPT(Profiles,Profiles[UserID],Profiles[Status]))

       

      If I want to fetch only those records where deleted = 0