Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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! 🙂
Hi @mueland ,
Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
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! 🙂
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |