Forum Discussion
New Measures corrupts data integrity
Hi there,
i have a very odd issue that i am struggling to describe or investigate.
i have merged 2 tables (booking journal and budgets) with an outer join (ISTSOLLVGL). Looking at the data, it all looks good to me:
for the current year, we have per month and account 7109 an entry in the budget table. plus we have one record from last year in the booking table.
For the current year i dont have a "counter booking" to create a match and for last year i dont have a budget. all ok.
Now i am creating a report with that table and another one which is related 1:n to this (filter on year = 2022).
in the lookup table, i have exactly one match for that account:
and i have linked that account to the ISTSOLLVGL account
in my report all lookss good:
the column Art comes from the lookup table, all the other fields are coming from SOLLISTVGL.
Now i want to create a calculated item. for the example it is simply:
Measure = 1
when i drag the column now in my report, my data gots repeated and a way i cannot understand:
For each entry (period + Account), i get an additional art = Ertrag which is nowhere defined.
can you help me please? somewhere in that setup i must have screwed up. but i cannot find where 🙂
thanks!!
4 Replies
- HotChilli
Community Champion
There's no great mystery.
Under "in my report all lookss good" one of the columns is a measure (it looks like SUM(Wert)). The values are being calculated for each row but there will be blank values for Art = Aufwand so powerbi removes them.
In the last table, there are two measures SUM(Wert) and Measure. Measure evaluates to 1 for any row (it's a constant) so powerbi displayes the row so you now see rows you didn't see before
------
Also, the first picture showing the join results does not look good to me at all. It shows me 2 unrelated tables jammed together. If I asked you what the primary key (in database terms) is for that table, what would it be?
Hi HotChilli , thank you for looking into that.
Actually the Column "Art" is in the table LU_KONTORAHMEN, which is joined to the merged table. so all entries for account 7109 should get the same "Art"-Value assigned.
Yes, "Wert" is measure - but even if i show all columns (which would lead to SUM(Wert) == Wert, because i am on the lowest aggragation level), this happens.
I understand the first picture of the table ISTSOLLVGL looks ugly. but this is a full outer join showing accounts with budgets and actual bookings. it is possible that we dont have a match, but still need those value in the report.
my join key is period, branch and account.
to avoid null values in my non-measure values, i have created coalesce-similiar columns, where i use a logical like this:
branchall=
if table1.branch is null, then use table2.branch
etc.
hope this makes sense 🙂 if i can provide you with any info (data dump ..) to support you help, let me know!
really appriciate it!
i have cleaned up a bit an just left the relevant columns:
ISTSOLLVGL
:
Lookup Table for column "ART":
both are in a N:1 relationship based on Konto<>KontoAll
- HotChilli
Post a link to the pbix and I'll have a look at it.
