Forum Discussion
Bi-directional filter, SUM issue
Hi,
Trying to understand the filtering in my report. I have two tables where Table A is my fact table and Table B is dimensions with attributes for WO.No. There are some issues with the quality of the data, so the relationship should have been 1 to 1 (table B filtering A), but for multiple reasons I can’t change as of now
Table B
Wo.No | Attribute 1 |
Blank | A |
014869 | A |
Blank | B |
014879 | C |
Table A - All Wo.No in Table A are also in Table B. No duplicates in Table A
Wo.No | Earned |
014869 | 41 |
014879 | 0 |
014880 | 1 |
014881 | 6 |
I want the Filtered Table B Wo.No<>Blank
Wo.No | Attribute 1 |
014869 | A |
014879 | C |
So, tried to filter, but then my total “Earned hours” changes. For Each Wo.No it stays unchanged, it’s only the total sum that is different. Any suggestions?
Also tried with a measure like:
Earned hrs = VAR wonos = DISTINCT ( Table B[WO.No] ) RETURN CALCULATE(SUM ( Table A'[Earned hours] );'Table A'[WO.No] IN wonos)
I think the bi-directional filter is playing me a trick. Really appreciate some input
Hi!
Thanks for your follow-up and sorry for my late reply. Was not an issue with the sum but in addition to having blank fields my table B was also missing values, so when filtering om blank I was also missing out some orders. Thanks for the follow-up either way! :)
3 Replies
Hi mueland ,
You can create measure like DAX below.
Earned total = CALCULATE(SUM(TableA[Earned]))
Or you needn't to create anything, just change the show aggregation.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi!
Thanks for your follow-up and sorry for my late reply. Was not an issue with the sum but in addition to having blank fields my table B was also missing values, so when filtering om blank I was also missing out some orders. Thanks for the follow-up either way! :)
