Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have the below table:
Table Source
Make | Model | Setting 1 | Setting 2 | Setting 3 |
Make 1 | Model 1 | a | z | aa |
Make 1 | Model 1 | a | z | aa |
Make 1 | Model 1 | a | z | aa |
Make 1 | Model 1 | a | z | bb |
Make 1 | Model 2 | a | z | aa |
Make 1 | Model 2 | a | z | aa |
Make 1 | Model 2 | a | z | bb |
Make 1 | Model 2 | a | z | bb |
Make 1 | Model 3 | a | z | aa |
Make 1 | Model 3 | a | z | aa |
Make 2 | Model 4 | b | z | aa |
Make 2 | Model 4 | b | z | bb |
Make 2 | Model 4 | b | z | cc |
Make 2 | Model 4 | b | z | aa |
Make 2 | Model 4 | b | z | bb |
Make 2 | Model 4 | b | z | cc |
Make 2 | Model 4 | b | z | aa |
Make 2 | Model 4 | b | z | aa |
Make 3 | Model 1 | a | y | bb |
Make 3 | Model 1 | a | y | aa |
Make 3 | Model 1 | a | y | bb |
i then create a visualisation that presents this:
Make | Model | Setting 1 | Setting 2 | No of Settings 3 |
Make 1 | Model 1 | a | z | 2 |
Make 1 | Model 2 | a | z | 2 |
Make 1 | Model 3 | a | z | 1 |
Make 2 | Model 4 | b | z | 3 |
Make 3 | Model 1 | a | y | 2 |
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)
Solved! Go to 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
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
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
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...
@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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |