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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
as65fgdax
Frequent Visitor

How to include a column from a related table in visual?

I am still figuring out Power BI. I have got seemingly a very simple task: make a visual table in report view include a column from another table.

I have got:
GL - a table with invoice information, including client ID

NC - a table with credit note information, including client ID

Clients - a table with client ID as a unique identifier

Tables are not merged, but I made relationships in model view via Clients table. In the report view, I have made a table based on GL, which shows distinct count of invoices per client. I have also included a calculated column showing a ratio of credit notes to invoices like this:

Ratio NC/INV =
 VAR ClientNumber = 'GL'[ClientID]
 VAR TotalInvoices =
        CALCULATE(
            DISTINCTCOUNT('GL'[Invoice number]),
            FILTER('GL', 'GL'[ClientID]=ClientNumber)
        )
 VAR TotalNC =
        CALCULATE(
            DISTINCTCOUNT('NC'[NC number]),
            FILTER('NC', 'NC'[ClientID]=ClientNumber)
        )
  RETURN IF(OR(TotalNC=0, TotalInvoices=0), 0, DIVIDE(TotalNC, TotalInvoices))
 
What I am not able to do is include a column in the same visual, showing distinct count of credit notes from NC. I tried as a calculated column and as a measure:
TotalNC = CALCULATE( DISTINCTCOUNT('NC'[NC number]), ALLEXCEPT('NC', 'NC'[Client ID]) ) 

Neither worked. Instead of giving a distinct count per line (per client), it gives me the total count of NC in every line. The only way it works correctly if I make a direct many-to-many relationship between NC and GL. But obviously then other things break down in my report 🙂 

Please help!




3 REPLIES 3
as65fgdax
Frequent Visitor

I tried this as a measure, as a column in GL and in NC, after I reference it in the visual with GL columns, I only get the grand total in every row 😕

Mahesh0016
Super User
Super User

@as65fgdax Please Try below Dax :

TotalNC =
CALCULATE (
COUNROWS(VALUES( 'NC'[NC number] ) ),
ALLEXCEPT ( 'NC', 'NC'[Client ID] )
)

> if its work please share your EndOutPut in table.

 

@as65fgdax Thank You!!

I ended up appending the two tables because the previous approach didn't seem to work.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors