Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Habres
New Member

Subtracting two counts

Hi everyone!

I have been trying for hours and searched for a solution, but I can't seem to get my dax code right. Maybe I not thinking in the right direction, because it does not seem so difficult. 

What I want is to subtract the number of cancellations on a certain date from the number of enrollments on that same date.

My table looks like this:

NUMBER_IDDATE_ID1DATE_ID2
1240604240605
2240605240607
32406050
42406050
5240606240607
6240606240606
72406060
8240607240608
9240607240608
102406080

 

So the result should be like this:

DATE_IDCOUNT_ID1COUNT_ID2RESULT
240604101
240605312
240606312
240607220
24060812-1


Now I can count the ID's seperately in two measures like this:

COUNT_ID1 =
CALCULATE(
    COUNT( 'Table'[NUMBER_ID] ),
    ALLEXCEPT( 'Table',
    'DATE_ID1 )
)


To get to the result I somehow have to selfjoin the Table. I tried visualising the table in the DAX Query View, so I would be able to then just make a column that subtracts COUNT_ID2 from COUNT_ID1

EVALUATE
   
VAR A =
    SELECTCOLUMNS (
        'Table',
        "DATE_ID", 'Table'[DATE_ID1],
        "COUNT_ID1", [COUNT_ID1]
    )
VAR B =
    SELECTCOLUMNS (
        'Table',
        "DATE_ID", 'Table'[DATE_ID2],
        "COUNT_ID2", [COUNT_ID2]
    )
VAR Result =
    NATURALLEFTOUTERJOIN ( A, B )
RETURN
    Result

But this code raises an error: Query (16, 34) An incompatible join column, (''[DATE_ID]) was detected. 'NATURALLEFTOUTERJOIN' doesn't support joins by using columns with different data types or lineage.

I think this is because not every DATE_ID from A matches with B. So then I wanted to do a full outerjoin, but that's where I got stuck...

Any ideas would be greatly appreciated!
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1717890334239.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1717890334239.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur thanks a lot for your solution! This is exactly what I needed!

Like in your example I made a date table from the first enrolment date until the last withdrawel date. I made two connections to the fact table, one on the enrolment date and one on the withdrawel date. The last one being inactive. 

 

In my measure for the number of withdrawels I defined the relationship with USERELATIONSHIP(  'Datetable'[DATE_ID], Facttable'[DATE_ID] ). 

 

Then I made another measure to subtract this measure from my existing measure for the enrolments count and thats's it!

 

In a table or visualisation I now use the date from the new date table to show the remaining enrolments.

 

Greatly appreciated!




You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Habres
New Member

Hi @tharunkumarRTK, thank for the reply!

This looks like a good workaround, but when I used this in my query it runs forever without giving output. So performance wise this is not working in my case.

Might there be another approach to calculate the result of COUNT_ID1 - COUNT_ID2 on the same DATE_ID?

@Habres 

I think there multiple ways to solve your requirement. You can create a seperate dim table with unique values of Date_ID1 and Date_ID2 and create a two relationships between the new table and your data table (one active and one inactive ralationship) and then write your count measures. You can use USERELATIONSHIP function when you want your measure to follow the inactive relationship.

 



Need Power BI consultation, hire me on UpWork .


If the post helps please give a thumbs up



If it solves your issue, please accept it as the solution to help the other members find it more quickly.




Tharun



tharunkumarRTK
Solution Sage
Solution Sage

@Habres 

 

If you want to use naturalouterjoin then both the columns should have same data lineage otherwise it wil fail. One other option/workaround is to remove the lineage by writing the column as an expression and then the join will work.

 

for example, look the below pattern, &"" is added to the column names to remove the data lineage. Follow this pattern and then it should work

 

DEFINE
    VAR StoresByCountry = 
        SELECTCOLUMNS (
            TREATAS ( { "Armenia", "Australia", "Denmark" }, Store[CountryRegion] ),
            "Country", Store[CountryRegion] & "",
            "NumOfStores", CALCULATE ( COUNTROWS ( Store ) )
        )
    VAR CustomersByCountry = 
        SELECTCOLUMNS (
            TREATAS ( { "Armenia", "Australia", "Denmark" }, Customer[CountryRegion] ),
            "Country", Customer[CountryRegion] & "",
            "NumOfCustomer", CALCULATE ( COUNTROWS ( Customer ) )
        )
        
EVALUATE StoresByCountry

EVALUATE CustomersByCountry

EVALUATE
    NATURALLEFTOUTERJOIN ( StoresByCountry, CustomersByCountry )
    
EVALUATE
    NATURALLEFTOUTERJOIN ( CustomersByCountry, StoresByCountry )
    
EVALUATE
    NATURALINNERJOIN ( StoresByCountry, CustomersByCountry )

 



Need Power BI consultation, hire me on UpWork .


If the post helps please give a thumbs up



If it solves your issue, please accept it as the solution to help the other members find it more quickly.




Tharun



 

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors