Forum Discussion

Habres's avatar
Habres
Regular Visitor
2 years ago
Solved

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!
Need Help

6 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    2 years ago

    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



     

  • Habres's avatar
    Habres
    Regular Visitor
    2 years ago

    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?

    • tharunkumarRTK's avatar
      tharunkumarRTK
      Super User
      2 years ago

      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



    • Habres's avatar
      Habres
      Regular Visitor
      2 years ago

      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!