cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

DAX for DISTINCT Count ID on Many to many relationship table

Hi All,

 

I have two tables with Many to Many relation, I am trying to create a column to find the distinct count of "Id" and group it by start and end time. I don't want it as a measure because I am going to use the value from the new column to compare it with another column.

 

TABLE A

ID ||  Start_Time ||  End_Time ||  15_Min_Time
1 ||   2020-01-02 10:01:00 ||  2020-01-02 10:08:00  ||  2020-01-02 10:00:00
2 ||  2020-01-02 10:01:00 ||  2020-01-02 10:08:00 ||  2020-01-02 10:00:00
3 ||  2020-01-02 10:06:00 ||  2020-01-02 10:12:00 ||  2020-01-02 10:00:00
4 ||  2020-01-02 10:18:00 ||  2020-01-02 10:30:00 ||  2020-01-02 10:15:00

 

TABLE B

Time  ||  Info_Value ||   15_Min_Time
2020-01-02 10:00:00 ||  2  || 2020-01-02 10:00:00
2020-01-02 10:01:00 ||  1  || 2020-01-02 10:00:00
2020-01-02 10:02:00 ||  3  || 2020-01-02 10:00:00
2020-01-02 10:03:00 ||  4  || 2020-01-02 10:00:00
2020-01-02 10:04:00 ||  5  || 2020-01-02 10:00:00
2020-01-02 10:05:00 ||  1  || 2020-01-02 10:00:00

 

TABLE A and TABLE B are having M:M relation with 15_Min_Time

 

 Expected Output:

I want a column with distinct count of id but grouped by Start_Time

ID ||  Start_Time ||  End_Time ||   15_Min_Time ||   New_Column
1  ||   2020-01-02 10:01:00 ||  2020-01-02 10:08:00  ||  2020-01-02 10:00:00  || 2
2 ||  2020-01-02 10:01:00 ||  2020-01-02 10:08:00 ||  2020-01-02 10:00:00  || 2
3 ||  2020-01-02 10:06:00 ||  2020-01-02 10:12:00 || 2020-01-02 10:00:00  || 1

 

I tried the following formula

New_Column = CALCULATE(DISTINCTCOUNT(Table A[id]), GroupBy(Table A[Start_Time], Table A[End_Time]))

Error --- Circular Dependency Error

 

New_Column = SUMMARIZE(Table A,Table A[start_time],Table A[End_time],"Distinct ID",DISTINCTCOUNT(Table A[id]))

Error ---- The expression refers to multiple columns.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

I resolved this issue by going through the article https://www.sqlbi.com/articles/understanding-circular-dependencies/ I used the ALLExcept function to overcome the issue.

New_Col =  CALCULATE(DISTINCTCOUNT(Table A[id]),GROUPBY(Table A,Table A[start_time], Table B[End_time]),ALLEXCEPT(Table A,Table A[Calculated_Col]))

NOTE:
Calculated_Col is a column that I created to get the average of Table B(Info_Value), power bi considers all the columns in the table while creating "New_Col", so I was getting a Circular Dependency error, ALLExcept resolves that issue.

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

@nikeshv First, I would highly recommend trying to get rid of any direct many-to-many relationships. Use a bridge table to connect those two tables and your life will be easier.

 

As for your errors. Your second one is because SUMMARIZE returns a table, not a single scalar value.

 

The first error, circular dependencies are difficult to resolve without more information. 


Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

@Greg_Deckler  Thanks for sharing the information, I will work on resolving the M to M cardinality, I have updated my question with more information, hope this will help in resolving the issue.

Highlighted

I resolved this issue by going through the article https://www.sqlbi.com/articles/understanding-circular-dependencies/ I used the ALLExcept function to overcome the issue.

New_Col =  CALCULATE(DISTINCTCOUNT(Table A[id]),GROUPBY(Table A,Table A[start_time], Table B[End_time]),ALLEXCEPT(Table A,Table A[Calculated_Col]))

NOTE:
Calculated_Col is a column that I created to get the average of Table B(Info_Value), power bi considers all the columns in the table while creating "New_Col", so I was getting a Circular Dependency error, ALLExcept resolves that issue.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors