Forum Discussion
Countrows of an Intersect result
Hi all,
I am trying to perform a left join (if null), like below:
Considering the following Table1:
| Date | ID |
| 2017-12-15 | 1 |
| 2017-12-15 | 2 |
| 2017-12-15 | 3 |
| ... | ... |
| 2018-01-15 | 1 |
| 2018-01-15 | 2 |
| 2018-01-15 | 3 |
| 2018-01-15 | 4 |
| 2018-01-15 | 5 |
What I want to know, is how much new IDs were added in 2018-01-15 compared to the previous month 2017-12-15 (should be 2, IDs 4 and 5 are presented in 2018-01 and were not in 2017-12).
To do so, I was first calculating previous month date, based on TODAY():
<p>"var __lastMonth = DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))"</p>
Then, I filtered the table to get all ID related to __lastMonth:
<p>var __previous =SUMMARIZE(FILTER('Table1','Table1'[Date]=__lastMonth),'Table1'[ID])</p>
I also filtered the table for current date:
<p>var __current =SUMMARIZE(FILTER('Table1','Table1'[Date]=TODAY()),'Table1'[ID])</p>
and performed an intersect to get their differences:
<p>var __intersect = INTERSECT(__current,__previous)</p>
This should generate the following table:
| ID |
| 1 |
| 2 |
| 3 |
And the result that I was looking for was:
<p>RETURN COUNTROWS(__current) - COUNTROWS(__intersect)</p>
The problem: If I reproduce this logic creating a "Table", I see that __intersect is outputing correctly the table, however, when I try this code as a "Measure", the __previous came empty, and so does the __intersect.
What is wrong? Is there a more efficient way to do this?
Thank you in advance.
The logic was working, there was a date filter in the page for current day, thats why it was not able to bring any value related to last month. Thank you for your replies.
4 Replies
nhowardResolver I
I assume you are working within PowerBI desktop. You might need to change the way you think about data; as PBI is not SQL, it does not do joins. The relationships are links that allow for filtering. One table filters the other table, reducing the number of rows that it has. You then perform a calculation on one of those columns. To complicate things further, when you put a measure into a visual, you are adding further filters (each row and column of a matrix is a filter).
Aussming that the ID column is always auto-incrementing by 1, then your measure could be based on the Difference between the MAX ID this month and the MAX ID last month. You don't mention that you have a master date table, so I'll assume you don't have one for this formula.
New Items this month = MAX(ID) - CALCULATE(MAX(ID),DATEADD(Date,-1,Month))
You then put this measure in a table or matrix with the month as a column or row.
MAX(ID) will be the month of the row. The Calculate uses the month of the row to obtain last month, and filters the table for that.
Hope that helps,
- MFelix
Super User
Hi EvertonRR ,
Try the following measure:
Measure = VAR LAst_Date = DATEADD ( Dates[Date]; -1; MONTH ) VAR date_select = MAX ( Dates[Date] ) RETURN IF ( MAXX ( FILTER ( ALL ( 'Join' ); 'Join'[Date] = LAst_Date && 'Join'[ID] = MAX ( 'Join'[ID] ) ); 'Join'[ID] ) > 0; 1; 0 )Then filter your visual by the 1 or 0.
I'm assuming that you don't have ID that dissapear from one date to the other.
Regards,
MFelix
PaulDBrownCommunity Champion
Try:
New ids in Month = VAR currentId = VALUES(Table1[ID]) VAR PrevMonthID = CALCULATETABLE(VALUES(Table1[ID]), DATEADD(Table1[Date], -1, MONTH)) RETURN COUNTROWS(EXCEPT(currentId, PrevMonthID))To give you this:
The logic was working, there was a date filter in the page for current day, thats why it was not able to bring any value related to last month. Thank you for your replies.
