Distrinct filter on text data

[Jriker1]

I have two tables that contains a number of fields however for this sake:
Country Table

• Country Name
• Others but nothing relevant here.

Application Table:

• Application Name
• Country Name (linked to Country Table but full name of country)
• Vendor

I am trying to create a PivotTable that shows the name of the country and then a distinct list of vendors they have worked with. In the Application Table the country could be repeated multiple times and the vendor could be used multiple tims for a country or countries. I want to have something that shows for country X, here is the distinct list of vendors they use, and a total vendor count. The data is incomplete so I want any blanks eliminated from display or count.
I've gotten close but can't figure out how to filter out blanks. Also if a country doesn't have any vendors, don't want it listed. Right now they return with a blank.
Thoughts?
Thanks.

 

[miguel.escobar]

try using DISTINCTCOUNT(Application Table[Vendor])

 

[Jriker1]

Thanks for the reply. I tried that already but returns the blank rows and also counts it in the total. Keep in mind also, technically I wouldn't want to show that measure as it will always be 1 with a total based on the number per country so listing 1 over and over for each vendor will not look good. Not sure if there is a way to remediate that.

Thanks.

 

[miguel.escobar]

You could just add the Country and the vendors as part of the row series or I'm not sure if you're trying to do a matrix of Countries against vendors and just have an X where the match is made. If that's the case then a simple count would do the job

If you happen to have a sample of the workbook so that I could work with it I could then work on it during the weekend or perhaps later tonight.

Best,
Miguel

 

[Jriker1]

Figured it out. Did a measure of:

=CALCULATE(DISTINCTCOUNT((Table_owssvr[Vendor])), filter(Table_owssvr,NOT(ISBLANK('Table_owssvr'[Vendor]))))

Only issue is I can see the count so getting output of:

Would love to keep the count next to the country however loose the constant 1's next to each individual entry since it will always be one.

Thanks.

Note: Clicking on the Measure 5 column and selecting to remove it leaves it in the calculated values section on PowerPivot field list, but seems to actually eliminate it's functionality so blank rows start showing again. Not sure if it's a bug. Would assume of you remove it from the PowerPivot chart, it would remove from the Field List.