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.
I have implemented many to many using Bridge table. (TableA *<--1 Bridge 1<->* TableB).
Now i have dropped an attribute from TableB and
DISTINCTCOUNT(TableA[Column1])
into a table visual. I was not able to match the individual row sum with Total row at the bottom of the visual. I know why i was not able to match because each row in TableA might be get shared across two rows in TableB (which is a double count of the same row), when it comes to Total row it is counted as one since we are using DistinctCount. I could use
SUMX(VALUES(TableB[Column1]),DISTINCTCOUNT(TableA[Column1]))
but if we have 5 columns in TableB i should write this SUMX 5 times by replacing the column value in the VALUES function and also it's not going to work because while calculating the Total row we can not get to know which column is being used in the visual so that that particular column should be used in SUMX(VALUES())). I tried using this
CALCULATE(DISTINCTCOUNT(TABLEA[Column1]_,Bridge)
It doesn't work either.
Is there any way that i could overcome this issue ? Thanks in advance.
Solved! Go to Solution.
@Anonymous
You may add CALCULATE as follows.
Measure = SUMX ( VALUES ( 'Sales Program'[Sales Industry] ), CALCULATE ( DISTINCTCOUNT ( Pipeline[Dim_OpportunityId] ) ) )
Would need some sample data that recreates the issue. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Also, this looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Thanks for the reply. I have attached the sample data. Please take a look. In Sales Program table i have only one attribute incase of sample but in my real data I have almost 10 attributes which are having the same issue. Please download the sample pbix file from this link Sample Data
@Greg_Deckler Can you please take a look at this ? Thanks! I have attached the sample pbix file.
@Anonymous
You may add CALCULATE as follows.
Measure = SUMX ( VALUES ( 'Sales Program'[Sales Industry] ), CALCULATE ( DISTINCTCOUNT ( Pipeline[Dim_OpportunityId] ) ) )
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |