Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TK421
Helper I
Helper I

Sum of a distinctcountnoblanks measure not going the way i want....

I have the below table:

Table Source

MakeModelSetting 1Setting 2Setting 3
Make 1Model 1azaa
Make 1Model 1azaa
Make 1Model 1azaa
Make 1Model 1azbb
Make 1Model 2azaa
Make 1Model 2azaa
Make 1Model 2azbb
Make 1Model 2azbb
Make 1Model 3azaa
Make 1Model 3azaa
Make 2Model 4bzaa
Make 2Model 4bzbb
Make 2Model 4bzcc
Make 2Model 4bzaa
Make 2Model 4bzbb
Make 2Model 4bzcc
Make 2Model 4bzaa
Make 2Model 4bzaa
Make 3Model 1aybb
Make 3Model 1ayaa
Make 3Model 1aybb

 

i then create a visualisation that presents this:

MakeModelSetting 1Setting 2No of Settings 3
Make 1Model 1az2
Make 1Model 2az2
Make 1Model 3az1
Make 2Model 4bz3
Make 3Model 1ay2

where No of Settings is a Measure containing DISTINCTCOUNTNOBLANKS(Source[Setting 3])


My trouble starts when i try to get the sum of No. of settings. It never gives me the correct value - it always seems to be wildly under the actual.

 

the above 2 tables are mini representations of my actual data 

 

I need this value to use with another column i have that measures which is the most consistent Setting 3 for each Make + Model + Setting1 + Setting2 group. (had help on here to finish it off)

 

Any help? do i need to switch to a column rather than a measure - if so how do you get a column to do what you need when the grouping is controlled by the visualisation?

 

(hope this makes sense)

1 ACCEPTED SOLUTION

@TK421 
Assuming you want to use the result in a card visual, you can follow the below pattern

Sumx(
addcolumns
(
Summarize(
Table,
Table[Make],
Table[Model],
Table[Setting 1],
Table[Setting 2]
), 
"DistnctCount", DISTINCTCOUNTNONBLANK([Table[Setting 3])
),
[DistinctCount]
)

 

if you want to keep it in the same table visual then remove the filters on the table.

Sumx(
addcolumns
(
Summarize(
ALL(Table),
Table[Make],
Table[Model],
Table[Setting 1],
Table[Setting 2]
), 
"DistnctCount", DISTINCTCOUNTNONBLANK([Table[Setting 3])
),
[DistinctCount]
)

 

 


If the post helps please give a thumbs up


If it solves your issue, please accept it as the solution to help the other members find it more quickly.


Tharun

 

View solution in original post

9 REPLIES 9
TK421
Helper I
Helper I

Figured it out - where you have [DistinctCount] as the 2nd part of SUMX - i swapped that out for a meausre of my own that does DISTINCTCOUNTNOBLANK of the Setting 3
Its now working and correctly summing the total of all the No of Setting 3

thanks for all your help

TK421
Helper I
Helper I

I need a sum of all the distinct counts, which should equal 10 (2+2+1+3+2) in this case - but nothing i try will get that. i have tried a variety of calculates, sumx and the like - it never gets it right.
i can't include the pbix as the above tables are extremely simplified versions of my own data which is sensitive. i wish i could share - sorry.

@TK421 
Assuming you want to use the result in a card visual, you can follow the below pattern

Sumx(
addcolumns
(
Summarize(
Table,
Table[Make],
Table[Model],
Table[Setting 1],
Table[Setting 2]
), 
"DistnctCount", DISTINCTCOUNTNONBLANK([Table[Setting 3])
),
[DistinctCount]
)

 

if you want to keep it in the same table visual then remove the filters on the table.

Sumx(
addcolumns
(
Summarize(
ALL(Table),
Table[Make],
Table[Model],
Table[Setting 1],
Table[Setting 2]
), 
"DistnctCount", DISTINCTCOUNTNONBLANK([Table[Setting 3])
),
[DistinctCount]
)

 

 


If the post helps please give a thumbs up


If it solves your issue, please accept it as the solution to help the other members find it more quickly.


Tharun

 

Would the above be measures or calc columns?

Its a measure. Dont forget to replace the table name with your actual table name.

 


If the post helps please give a thumbs up


If it solves your issue, please accept it as the solution to help the other members find it more quickly.


Tharun

 

almost there - swapped everything over from my table names but the last
[DistinctCount] reference is redlined
..and..
overal error - Function ADDCOLUMNS expects a column name argument number 4

@TK421 

can you share the pbix file?

 

as mentioned - i cant share due to the nature of the data.

from the looks of it -its trying to find a field that is called DistinctCount

most i can do is post a screenshot of the error...Screenshot 2024-04-11 145910.png

tharunkumarRTK
Solution Sage
Solution Sage

@TK421 

Your measure [no.of settings 3] is giving the appropriate results as per its functionality, what is your expected result ?and share the pbix if possible.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors