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 a dataset of trap collections. The dataset has the species collected "species" and the number of nights the trap was running "trap_nights". One collection can have multiple rows of data where the "trap nights" number is repeated because each species gets its own row. So, as in the pic below, that one highlighted trap (Bayonne Park) was running for 6 nights, but if I Sum the "trap_nights" it looks like the trap was running for 18 nights becuse it has 3 rows of data.
If I don't Sum "trap_nights" and filter the data in a table, I get the correct numbers, but then I can't get totals.
I can set the "trap_nights" column to minimum, which also gives me the correct values and a total, but the total isn't a sum. It's just the minimum value for the column.
I also tried the group-by function but then I lose columns I need for other visuals.
I need to know the total trap nights over a given date range. How can I calculate the correct trap night numbers considering the repeated values?
Solved! Go to Solution.
Hi @entomophile,
Sorry about that. I had used the wrong column name in the formula 😅. Try this:
Trap Sum =
IF (
HASONEFILTER ( NJLT[site_name] ),
MIN ( NJLT[trap_nights] ),
SUMX (
SUMMARIZE (
NJLT,
NJLT[site_name],
NJLT[collection_date],
"MinOfValues", MIN ( NJLT[trap_nights] )
),
[MinOfValues]
)
)
Something like...
measure = SUMX(Table,min(trap_nights))
That doesn't seem to work. It still sums each row.
Ah yes, you need to group it on the site_name.
Trap Nights =
SUMX ( SUMMARIZE ( NJLT, NJLT[Site_name] ), MIN ( Trap_nights ) )
Getting closer. That expression gives the correct numbers, but I still can't get a correct sum.
Looks like it needs the collection date as well.
Trap Nights =
SUMX ( SUMMARIZE ( NJLT, NJLT[Site_name],NJLT[Collection_date] ), MIN ( Trap_nights ) )
Same issue. Correct numbers, wrong sum.
Hi @entomophile
Try this:
Trap Sum =
IF(
HASONEFILTER(NJLT[site_name]),
MIN(NJLT[site_name]),
SUMX(VALUES(NJLT[site_name]),MIN(NJLT[site_name])
)
)
If my syntax is correct, that expression does not work.
Hi @entomophile,
Sorry about that. I had used the wrong column name in the formula 😅. Try this:
Trap Sum =
IF (
HASONEFILTER ( NJLT[site_name] ),
MIN ( NJLT[trap_nights] ),
SUMX (
SUMMARIZE (
NJLT,
NJLT[site_name],
NJLT[collection_date],
"MinOfValues", MIN ( NJLT[trap_nights] )
),
[MinOfValues]
)
)
That's it! Thank you so much, I never would have figured this out on my own.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
103 | |
77 | |
71 |