Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
@Anonymous,
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 |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
101 | |
92 | |
74 | |
60 | |
59 |