Forum Discussion
Solved
Count Rows from another table based on date filter and group by column
Hi,
Can anyone help me in solving my below problem.
I have two tables TableA and TableB
TableA
| Category | ID | Date |
| FactoryA | 1234 | 7/12/2018 0:00 |
| FactoryA | 1245 | 7/13/2018 0:00 |
| FactoryA | 12165 | 7/17/2018 0:00 |
| FactoryB | 12678 | 7/24/2018 0:00 |
| FactoryB | 12679 | 8/15/2018 0:00 |
| FactoryB | 12680 | 8/21/2018 0:00 |
| FactoryB | 12681 | 2/19/2019 0:00 |
| FactoryC | 12682 | 8/6/2018 0:00 |
| FactoryC | 12683 | 8/7/2018 0:00 |
| FactoryC | 12684 | 9/13/2018 0:00 |
| FactoryC | 12685 | 10/30/2018 0:00 |
| FactoryD | 12686 | 7/6/2018 0:00 |
| FactoryD | 12687 | 7/19/2018 0:00 |
| FactoryD | 12688 | 7/21/2018 0:00 |
| FactoryD | 12689 | 8/3/2018 0:00 |
| FactoryE | 12690 | 7/31/2018 0:00 |
| FactoryE | 12691 | 8/3/2018 0:00 |
| FactoryE | 12692 | 8/15/2018 0:00 |
| FactoryE | 12693 | 8/18/2018 0:00 |
| FactoryF | 12694 | 8/30/2018 0:00 |
| FactoryF | 12695 | 9/18/2018 0:00 |
| FactoryF | 12696 | 11/1/2018 0:00 |
| FactoryF | 12697 | 11/22/2018 0:00 |
Table B
| Category | Id | Date |
| FactroyA | 1939 | 7/13/2018 0:00 |
| FactroyA | 1940 | 7/17/2018 0:00 |
| FactroyA | 1941 | 7/28/2018 0:00 |
| FactoryB | 1942 | 9/17/2018 0:00 |
| FactoryB | 1943 | 9/21/2018 0:00 |
| FactoryB | 1944 | 10/8/2018 0:00 |
| FactoryB | 1945 | 10/16/2018 0:00 |
| FactoryC | 1946 | 8/6/2018 0:00 |
| FactoryC | 1947 | 8/7/2018 0:00 |
| FactoryC | 1948 | 9/13/2018 0:00 |
| FactoryC | 1949 | 10/30/2018 0:00 |
| FactoryD | 1950 | 7/19/2018 0:00 |
| FactoryD | 1951 | 7/21/2018 0:00 |
| FactoryD | 1952 | 9/29/2018 0:00 |
| FactoryD | 1953 | 10/11/2018 0:00 |
| FactoryD | 1954 | 10/19/2018 0:00 |
| FactoryE | 1955 | 7/31/2018 0:00 |
| FactoryE | 1956 | 8/15/2018 0:00 |
| FactoryE | 1957 | 8/18/2018 0:00 |
| FactoryF | 1958 | 8/30/2018 0:00 |
| FactoryF | 1959 | 11/1/2018 0:00 |
| FactoryF | 1960 | 11/22/2018 0:00 |
Now, i would like to calculate the count of rows in tableB when Table A Category column matches with TableB Category column. So, my output should apppear like this
Output :-
| Category | TableACount | TableBCount |
| FactoryA | 3 | 3 |
| FactoryB | 4 | 4 |
| FactoryC | 4 | 4 |
| FactoryD | 4 | 5 |
| FactoryE | 4 | 3 |
| FactoryF | 4 | 3 |
I am able to acheive the TableB count by using the below dax
Output = calculate(countrows(tableB), Filter(TableB, TableB[Category] = TableA[Category])), but my output should change when I apply the date filter on TableA, my tableB count should also change based on the date slicer applied on TableA filter.
Note :- Both of my tables are not related to each other
Appreciate your help!
Thanks
3 Replies
Thank you. Worked like a charm !
You are welcome.
