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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Habres
Regular Visitor

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
Regular Visitor

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
Super User
Super User

@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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.