Is it possible to create a formula that will only bring back Provider Names A through M?

[jonnyA]

I created a Power BI report for a client, but the data in the table was going beyond the bottom of the page.

The Client wants the data to all be on 1 page.

So, I created 2 separate tables.  One table with Provider Names A through M.  Then another table that brings back Provider Names N through Z.  

I created the tables by using the Basic Filter option under Filters.  The problem with Basic Filtering is that new Provider Names are always being added, so this would result in a lot of manual upkeep to these tables.

Is it possible to create a formula that will only bring back Provider Names A through M?  Then another formula for N through Z?

I assume you will only need the dataset name and Provider Name to write the formula. 

If there is anything else I am forgetting let me know and I will provide the information you need!

Thank you in advance!

[Anonymous]

Hi @jonnyA 

I create a sample , you can see if it can meet your needs .

(1)In Power Query , use Extract function to return the first letter of the name .

(2)Go back to Desktop view ,and then create two tables with DAX .

A-M = CALCULATETABLE('Table','Table'[First Characters] in {"a","b","c","d","e","f","g","h","i","j","k","l","m"})

N-Z = CALCULATETABLE('Table','Table'[First Characters] in {"n","o","p","q","r","s","t","u","v","w","x","y","z"})

You will get two tables like the screenshot :

You can also add slicers with column [First Characters] field from corresponding tables .

I have attached my pbix file ,you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

[samdthompson]

Hello, I had to make some assumptions around your data structure. I am assuming you have say a fact table and a related supplier table. The formula I described earlier would be on the supplier table. If you only have the one giant table, you could put it there but I would reccomend splitting out dimensions from facts.

Cheers

[samdthompson · Accepted solution]

Hello, you could attack this with a calculated column on the supplier table. Something like

LetterGroup=
VAR letter= LEFT(SupplierTable[SupplierName],1)
RETURN
SWITCH( letter,"a","a-m","b","a-m"........."n","n-z","o","n-z".....)

then just use that as a filter on each page. Alternately you could do that all in power query.

Cheers,

[jonnyA]

@samdthompson 

Thank you for your response.  

But what would the Formula look like?