Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi! I have 2 tables:
Table A: document | start date | end date | Yearmonth
Table B: document | date | Yearmonth
Table A had duplicates so I created a referenced table to table A grouping by document, min(start), max(end), this way I now have 1 document per row, and I created a relation between this and B on document.
Not every field in B has a document in A.
I need to know how many of the records in B have a date between A[start date] and A[start date].
I tryied a lot of things but the simplest of all I can't directly compare values using IF.
Any ideas?
Thank you!
Solved! Go to Solution.
Ok, I solved like this:
New column-> Time_ok = IF ( 'Table B'[date]< RELATED ( 'Table A'[max_date] ) ) ;1;0 )
And new measure to get the percentages:
New Measure -> % tiempo = DIVIDE(SUM('Table B'[Time_ok]);count('Table B'[document]))
It seems to work, buy I'm new to Power BI so I'd like to know if there is a better solution.
Thank you!
Ok, I solved like this:
New column-> Time_ok = IF ( 'Table B'[date]< RELATED ( 'Table A'[max_date] ) ) ;1;0 )
And new measure to get the percentages:
New Measure -> % tiempo = DIVIDE(SUM('Table B'[Time_ok]);count('Table B'[document]))
It seems to work, buy I'm new to Power BI so I'd like to know if there is a better solution.
Thank you!
You may also try SUMX Function to add a measure directly.
Great! but I think will lost readeability if I continue to nest functions (because I'm new and want to understand my code in 2 weeks, when I advance furter I'll begin using your method), thanks!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |