Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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_ID | DATE_ID1 | DATE_ID2 |
1 | 240604 | 240605 |
2 | 240605 | 240607 |
3 | 240605 | 0 |
4 | 240605 | 0 |
5 | 240606 | 240607 |
6 | 240606 | 240606 |
7 | 240606 | 0 |
8 | 240607 | 240608 |
9 | 240607 | 240608 |
10 | 240608 | 0 |
So the result should be like this:
DATE_ID | COUNT_ID1 | COUNT_ID2 | RESULT |
240604 | 1 | 0 | 1 |
240605 | 3 | 1 | 2 |
240606 | 3 | 1 | 2 |
240607 | 2 | 2 | 0 |
240608 | 1 | 2 | -1 |
Now I can count the ID's seperately in two measures like this:
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
Solved! Go to Solution.
Hi,
PBI file attached.
Hope this helps.
@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.
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
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
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |