Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MitaSaxena
Frequent Visitor

Help with Count

Hi,

Will really appreciate any suggestions; new to Power BI and have tried everything.

I want to run a report on how many people have used an access card point, but to exclude a group who works everywhere. I have an eventslog table which has access card 'carrierobjectid' which links with the carrier (staff list); and carrier list table has personnel number which then links with CardHolder table. I have built the relationships.

When I use Table visualisation - the data looks correct - I have column for date, reader name, and library card number (which excludes the excluded group). My issue is that if I turn it into a graph or matrix so that I can count the card numbers, it counts all the personnel number from carrier table, or all the card numbers from CardHolder table.  So on table - I see 11 records, but on graph there are over 60k!

I cannot count carrierobjectids from eventslog because I have no way to exclude the numbers I need to exclude.

Any suggestion will be much appreciated.

Thanks

Mita

 

MitaSaxena_0-1611768182176.png

 

MitaSaxena_1-1611768553222.png

 

 

1 ACCEPTED SOLUTION

Hi, I wanted to delete this off, since I am now sorted... I gave us trying directquery with SQL and used excel instead. Instead of relate function used lookupvalue which worked well.

lookupvalue does not work on directquery unless this is done, which has been blocked by the organisation.

View solution in original post

3 REPLIES 3
vanessafvg
Super User
Super User

can you share more information, is it possible to share the pbix or sample data (in text format)

no doubt the relationships you have created might not have been set up correctly , another way to do it is also to denormalise the table ie merge your tables in power query into one table.  Also your bi directional relationship might be creating an issue.

 

sharing some sample data in text form would be useful of all the tables





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi,
Thank you so much for your reply, in an effort to replicate the issue and create pbix example, I was able to resolve by using relate and then distinctcountnoblank in the sample file; but in the actual file even though there is 1:1 relationship, when relate is used - it gives the selection, but then an error is generated as per the image below.
Any suggestion would be gratefully received.
Thanks

Mita

MitaSaxena_0-1611854485044.png

 

Hi, I wanted to delete this off, since I am now sorted... I gave us trying directquery with SQL and used excel instead. Instead of relate function used lookupvalue which worked well.

lookupvalue does not work on directquery unless this is done, which has been blocked by the organisation.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.