Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have data model with three tables:
and here is data from these tables:
and now I want to create report with one slicer with nameA values, and one table, with nameB column and number of rows in table C, so I created measure: Measure = COUNTROWS(Table C) + 0 (I added +0 at the end because I want to show 0 instead of BLANK when there are no rows in Table C that match selection). Something like this:
and here is the problem, when I select some value from slicer, "a" for example, this is what I get:
but that is not what I expected, I just want to see:
a | 0 |
aa | 1 |
aaa | 2 |
So slicer is filtering values from table C and counting them but it does not filter nameB column values.
I tried removing +0 from the measure and now it is returning just
a | |
aa | 1 |
aaa | 2 |
as expected.
Can someone please explain me what is happening here, and how I can get wanted result.
Solved! Go to Solution.
You can try doing
Measure = SUMX(TableB, 0 + COUNTROWS(C))
Adding +0 to a measure can act as a cross join or clear filters when multiple table relationships are involved.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Kudo'd OP for a really well-presented and clear request. An example of how help requests should look!
Pete
Proud to be a Datanaut!
You can try doing
Measure = SUMX(TableB, 0 + COUNTROWS(C))
Adding +0 to a measure can act as a cross join or clear filters when multiple table relationships are involved.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thank you very much @AllisonKennedy , you helped me a lot. Your solution is working well.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |