data not pulling throught due to relationships

[bigsol84]

At the moment when i choose a particular year from the date visualisation ( can filter down to quater, months etc..), its not pulling all the data through. At the moment there are 864 people supported but when i choose 2022 for example there should be 161 but it shows 186. ive also used a measure to try and sort it: 

SignedUp Count = CALCULATE(

DISTINCTCOUNT('mft SV Emp& Skills Part & Ref'[Participant Ref]),

ALL('Calendar'[TheDate]),

USERELATIONSHIP('MMM-YY'[MMM-YY],

'mft SV Emp& Skills Part & Ref'[Signed in programme])).

Ive also added another table to link between the calendar table and the mft SV Emp&skills table (people report) but no luck

Any advice?

any help appreciated

[HotChilli]

If I was you, I would create a summary table in Excel similar to the table you have in powerbi.  Also, order the table (above) in Excel by date 'signed in programme' so that you can cross reference with the powerbi table.

--

In Powerbi, create another table with the actual participant reference (not a count) and 'signed in programme' field.  Work through it by Month comparing it to the excel table.  You might also want to use Data View in Powerbi to filter from the column header for each month.  The No of Records will show at the bottom of the screen.

--

Start with the first month. If there are x records in Excel but more than x records in powerbi, narrow the investigation by looking at individual records.

--

I notice that there are 3 records in powerbi shown in the table which don't have a 'signed in programme' value.  Are they included in the Excel total?

--

This shouldn't take too long.  You only need to find 1 or 2 records which are shown in powerbi but not excel and you investigate those to see where the problem lies.

Once you find the 'problem' , invsetigate if all the 'extra' records are caused by the same issue.

[bigsol84 · Accepted solution]

hi @HotChilli 

it seem by removing a relationship between calendar and mft sv emp and by adding MM-YYtable as a go between seems to have got it working

[HotChilli]

We need more detail to help you.

You are talking about the slicer in the top left corner (to select Year) I think.  What table/column is this from?

'People Supported', is that a measure?   What is the measure definition?

-

How do you know "when i choose 2022 for example there should be 161 but it shows 186"? If that information is accurate, then (on a test page) you should be able to create a slicer and a list of all the People supported in a table visual.  You can then investigate where the extra values are.

[bigsol84]

Hi @HotChilli 

The slicer comes from the calendar table. The people supported is just card with a count of people signed in the programme. when i did a check on the dashboard to the excel file used for the data then it is different totals. 

with people supported it was just a standard count then i thought id try something else i.e measure and both pulling same results

Yes ive being doing a check on a test page and trying to work out the extra values 

[bigsol84]

@amitchandakany help would be appreciated