Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filters

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

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.