Filters

[Anonymous]

Hey everyone

I am very new to power bi especially DAX. I have a question. 

So I am using a small subset of adventure works database. and what i want to do is 

1. Find out top 10 customer per region.

I have 4 tables

Dim Customer(First Name,LastName etc)

Dim Geography(CountryRegionCode,City,StateProvince etc)

DimProductCategory(EnglishProductCategoryName,FrenchProductCategoryName etc)

FactInternetSales

What I want to do is

I want to use matrix table . I want to show it like this

Rows:-

Full Name(I have created the dax for full name)

followed by calculated column productcategoryname.

(so the calculation should be as follows:-

if the customer is a french customer then FrenchProductCategoryName should be selected else EnglishProductCategoryName

should be selected)

from DB prospective DimCustomer is joined by DimGeography table using Geography Key.

Columns:-

EnglishCountryRegionName

FrenchCountryRegionName

Values:- SalesAmount

Is it possible to achieve this in dax. I think we can create a slicer to achieve it, but I dont want that. I want this purely in dax.

Not sure how. Any help can be appreciated.

Regards

Farhan Jamil

[Anonymous]

@Anonymous 

There is a option in the filter panel where you can set top or bottom N value of a field.

With just text descritpion, it is difficult to understand your model and you are unlikely get a correct solution for this kind of multi-table model question that requires complex dax calculations. I would recommend you to post with a sample pbix, so we can test on it.

Read this post to see how to get your answers quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards
Paul

[Anonymous]

Hey Paul

Apologies for coming back late. But Basically as mentioned earlier I have a small subset of adventure works database which has tables like dimcustomer,dimgeography,dimproduct,dimproductcategory,dimproductsubcategory,factinternetsales. 

I am creating a visual which is top 10 customer per sales amount per region

and what I want is 

If i have a french customer then I want frenchproductcategorylabel to show up, if english then english. This can be achieved in sql by putting a case statement like 

case when b.CountryRegionCode = 'FR' then f.FrenchProductCategoryName else f.EnglishProductCategoryName end as 'ProductCategory',

I am not sure how to achieve this in power bi. 

I dont know how to attach .pbix file in this community as i am very new to power bi and this community.Hope I am clear in the requirement 

Screenshot

screenshot

for example adriana gonzalez, she is french customer, so i want the french productcategory detail label to show up, for Jordan turner I need english product category detail label to show up.  I am using matrix to show the visual.

[Anonymous]

HI Paul 

I think i figured out how to attach .pbix file. I have attached it in one drive.Hopefully it should be ok for you to download

https://onedrive.live.com/?id=48C622BA403CA81%21103&cid=048C622BA403CA81 

Hope their is a clear picture now

Regards

Farhan JAmil