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.
hello everyone!
I have the following problem, I can't figure out how to solve it.
I have 2 tables Cost and Rent, both linked via a calendar table
I need to sum the values from Cost and Rent only when they are present in the Cost table, otherwise 0,
a simple calculation works fine for single month selected,
Date | Cost |
01-01-2023 | 100 |
01-03-2023 | 150 |
Date | Rent |
01-01-2023 | 50 |
01-02-2023 | 100 |
01-03-2023 | 150 |
Solved! Go to Solution.
Try this version
Total costs =
CALCULATE (
SUMX (
DISTINCT ( Costs[Date] ),
VAR CurrentDate = Costs[Date]
RETURN
CALCULATE ( [Costs] + [Rents], TREATAS ( { CurrentDate }, 'Calendar'[Date] ) )
),
'Acc'[Code] = "160"
)
Try
Total Cost =
CALCULATE (
[cost] + [rent],
CROSSFILTER ( 'Date'[Date], 'cost table'[Date], BOTH )
)
How about
Total Cost =
CALCULATE (
[cost] + [rent],
TREATAS ( VALUES ( 'cost table'[Date] ), 'Date'[Date] )
)
this one works, but only when I apply Acc[Code] = "160" to a visual, I tried to add it into Calculate as filter, it didn't help, only helps when it's applied to the matrix.
is it possible to insert it into the measure ?
Yes, you can use
Total Cost = CALCULATE (
[Costs] + [Rents],
'Acc'[Code] = "160",
TREATAS ( VALUES ( 'Costs'[Date] ), 'Date'[Date] )
)
but if i apply it to the visual it does work properly
What does your model look like ? I tried it with
and it worked. Also, at what level of the hierarchy is the account code ?
it seems to be the sames
Try putting a couple more measures into your matrix,
Dates with acc =
CALCULATE (
COUNTROWS ( 'Calendar' ),
'Acc'[Code] = "160",
TREATAS ( VALUES ( 'Cost'[Date] ), 'Calendar'[Date] )
)
Dates without acc =
CALCULATE (
COUNTROWS ( 'Calendar' ),
TREATAS ( VALUES ( 'Cost'[Date] ), 'Calendar'[Date] )
)
See what they give both with and without a filter on the visual.
this is when filter applied to the matrix
and this is when not
It looks like its completely ignoring the filter inside the CALCULATE. Not sure if this will make any difference but try
Total Cost =
VAR MonthsWithCost =
CALCULATETABLE ( VALUES ( 'Costs'[Date] ), 'Acc'[code] = "160" )
VAR Result =
CALCULATE (
[Costs] + [Rents],
'Acc'[Code] = "160",
TREATAS ( MonthsWithCost, 'Date'[Date] )
)
RETURN
Result
it calculated fine, but I noticed that totals didn't change
how could this be ?
Try this version
Total costs =
CALCULATE (
SUMX (
DISTINCT ( Costs[Date] ),
VAR CurrentDate = Costs[Date]
RETURN
CALCULATE ( [Costs] + [Rents], TREATAS ( { CurrentDate }, 'Calendar'[Date] ) )
),
'Acc'[Code] = "160"
)
somehow it returns the same
Its possible that the total is actually correct. Export the data to Excel and see what it comes up with as the total.
in excel total is correct
Check your costs table for entries with a date but either blank or 0 cost.
doesn't have any blanks or zeros
I meant where the cost was either blank or 0, not the date.
Is it possible to share a PBIX with any confidential info either removed or anonymised ?
Yesterday I spent the whole evening deleting confidential data so that I could share the file, it turns out that without this confidential information it works fine, so I guess the problem is in the source data.
p.s.
I deleted all the blanks and zeros, double-checked everything, it didn't help.
Perhaps you have other suggestions?
btw, accepted your solution, thanks a lot for help 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
27 | |
24 | |
23 |