Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
What I thought would be an easy tasked proved more complex then I thought
Table A
itemSK | count |
a | 5 |
b | 5 |
c | 6 |
d | 6 |
a | 7 |
b | 6 |
b | 1 |
d | 3 |
Item table
ItemSK | Mainitemid |
a | o |
b | o |
c | p |
d | p |
Result
Item | Count by mainitem |
a | 24 |
b | 24 |
c | 15 |
d | 15 |
Help me write dax for "Count by mainitem" please 🙂
I could give you all my filed attempts but i don't think it will help(?)
regards
Solved! Go to Solution.
Hi @Anonymous
See the attached PBIX.
First, you should ensure the model is set up like this (which I imagine it already is):
Then create this measure:
Count by mainitem =
CALCULATE (
SUM ( 'Table A'[count] ),
REMOVEFILTERS ( 'Item' ),
VALUES ( 'Item'[Mainitemid] )
)
This measure sums 'Table A'[count] for the values of 'Item'[Mainitemid] corresponding to the visible rows of 'Item', but removing any other filters on the 'Item' table.
Regards
Hi @OwenAuger,
Thanks for your quick reply.
I am getting an error on REMOVEFILTERS,
"Cannot find name 'REMOVEFILTERS'
'REMOVEFILTERS' is not a function."
I am using direct query to access data and there is a connection between the tabels like you describe (My model is more complex then this but I scaled down to only highlight the importnat parts.
I replaced REMOVEFILTER with ALL and from what i can se this is giving me the deciered result.
Thanks
Glad to hear it's working (using ALL rather than REMOVEFILTERS).
These functions are indeed equivalent when used in this way.
If REMOVEFILTERS is unavailable, I'm guessing you are connecting to an older version of Analysis Services Tabular.
For reference, the compatibility for DAX functions can be found on dax.guide:
https://dax.guide/removefilters/
Hi @Anonymous
See the attached PBIX.
First, you should ensure the model is set up like this (which I imagine it already is):
Then create this measure:
Count by mainitem =
CALCULATE (
SUM ( 'Table A'[count] ),
REMOVEFILTERS ( 'Item' ),
VALUES ( 'Item'[Mainitemid] )
)
This measure sums 'Table A'[count] for the values of 'Item'[Mainitemid] corresponding to the visible rows of 'Item', but removing any other filters on the 'Item' table.
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |