Forum Discussion

snph1777's avatar
snph1777
Icon for Helper V rankHelper V
1 year ago
Solved

DAX - measure filtered by another measure with SUMMARECOLUMNS - equivalent of T-SQL GROUP, HAVING,..

I have the below scenario: I have a table called EmployeeTable; it has 16 distinct employees, but each employee can have more than one row, based on the number of jobs she has had ; say, Geor...
Need Help
  • snph1777's avatar
    snph1777
    1 year ago

    @lbendlin

     

    Thanks for your idea; I believe I got this; only thing, you did not include the Job in your formula; in my actual project, I have duplicate of employee names and jobs; so it is essential I use DISTINCTCOUNT function for any type of count.

     

     

     

     

     

     

     

    Active Multi Job1_Measure

=

VAR x_CT = SUMMARIZECOLUMNS(

                             EmployeeTable[EmployeeId],

                             TREATAS({"Y"},EmployeeTable[IsActiveEmployee]),

                             "DistinctCountOfJobs_Measure",DISTINCTCOUNT(EmployeeTable[Job])

                            )

RETURN

COUNTROWS(

           FILTER(

                   x_CT,

                   [DistinctCountOfJobs_Measure] > 1

                 )

          )

     

     

     

     

     

     

    Can you validate the above code, whether it is fool-proof ? I tried this new measure in the Power BI file, it is good. Your idea to use SUMMARIZECOLUMNS proved very useful; I used the syntax from here.

snph1777's avatar
snph1777
Icon for Helper V rankHelper V
1 year ago

Thanks   lbendlin

 

Does this also work when the IsActiveEmployee value changes across Jobs ? Say, I have James and Jack below, I want Jack to be included, but not James, even though both have multiple jobs; Jack has had two jobs while being active, while James has had only one job while being active.

 

 

Now, I need the answer to change to 4. Is your DAX code a fool-proof solution to such changes ?

 

(I know, I did not post this scenario earlier, because I wanted the question to be as simple as possible, to start with.)

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

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      1 year ago

      yes, same formula still works.

      • snph1777's avatar
        snph1777
        Icon for Helper V rankHelper V
        1 year ago

        @lbendlin

         

        I have another scenario, where there are duplicate entires. I need to have DISTINCTCOUNT.

        (EmployeeId and EmployeeName are tied to each other though; an EmployeeId always refers to one EmployeeName only)

         

         

        See the Power BI and Excel here.

         

        I think I am good; but just in case, if you anything to comment, please let me know.

         

        It was really great to learn a very common applucation of SUMMARIZECOLUMNS.

    • snph1777's avatar
      snph1777
      Icon for Helper V rankHelper V
      1 year ago

      @lbendlin

       

      Thanks for your idea; I believe I got this; only thing, you did not include the Job in your formula; in my actual project, I have duplicate of employee names and jobs; so it is essential I use DISTINCTCOUNT function for any type of count.

       

       

       

       

       

       

       

      Active Multi Job1_Measure

=

VAR x_CT = SUMMARIZECOLUMNS(

                             EmployeeTable[EmployeeId],

                             TREATAS({"Y"},EmployeeTable[IsActiveEmployee]),

                             "DistinctCountOfJobs_Measure",DISTINCTCOUNT(EmployeeTable[Job])

                            )

RETURN

COUNTROWS(

           FILTER(

                   x_CT,

                   [DistinctCountOfJobs_Measure] > 1

                 )

          )

       

       

       

       

       

       

      Can you validate the above code, whether it is fool-proof ? I tried this new measure in the Power BI file, it is good. Your idea to use SUMMARIZECOLUMNS proved very useful; I used the syntax from here.