Forum Discussion

hidenseek9's avatar
hidenseek9
Post Patron
9 years ago
Solved

How to create a filtered table

Hello Power BI Community,

 

I am having a trouble creating a filtered table from one big table.

In my big file, I have many columns of data, but I just would like to create

a new table with below three columns using DAX.

What is the right DAX expression to achieve this??

 

I would like to create a table with columns

1) Type, 2) Formula, and 3) Brand

 

From the big file, first I want to extract a data with "Type" A.

Now I have a data set with just Type A.

Then I want to distinct Formula, so I have a unique set of Formula for Type A.

Then I would like to have brand information associated with Formula.

 

Dummy Data

 

My desired output should look like this dummy data.

 

Appreciate your help.

 

Many thanks,

 

H

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi,

     

    If I understand correctly you want a uique list for the columns Type, Formula and Brand? 

     

    I cannot reach your dataset because it is on your SharePoint.. also the url can be seen maybe good to know.

    Based on this "Big table".

     

    Try this formule:

    SmallerBigTable = 
SUMMARIZE(
	BigTable;
	BigTable[Type];
	BigTable[Formula];
	BigTable[Brand]
)

    Result:

     

    If your goal is to get the distinct columns you can also use Power Query, select the columns and choose "Remove duplicates".

     

    My above example is a New Table based on a DAX expression. This comes handy when you also when to do a calculation for this table. Doing that it is suggested to use ADDCOLUMNS instread of doing the calculation in the SUMMARIZE function.

     

    Example:

    SmallerBigTable =
ADDCOLUMNS (
    SUMMARIZE ( BigTable; BigTable[Type]; BigTable[Formula]; BigTable[Brand] );
    "Count of rows"; COUNTA ( BigTable[A] )
)

    Hope this helps!

  • Anonymous's avatar
    Anonymous
    9 years ago

    hidenseek9,

     

    Try this:

     

    SmallerBigTable = 
SUMMARIZE(
	FILTER(
		BigTable;
		BigTable[Type]="A"
		);
	BigTable[Type];
	BigTable[Formula];
	BigTable[Brand]
)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi,

     

    If I understand correctly you want a uique list for the columns Type, Formula and Brand? 

     

    I cannot reach your dataset because it is on your SharePoint.. also the url can be seen maybe good to know.

    Based on this "Big table".

     

    Try this formule:

    SmallerBigTable = 
SUMMARIZE(
	BigTable;
	BigTable[Type];
	BigTable[Formula];
	BigTable[Brand]
)

    Result:

     

    If your goal is to get the distinct columns you can also use Power Query, select the columns and choose "Remove duplicates".

     

    My above example is a New Table based on a DAX expression. This comes handy when you also when to do a calculation for this table. Doing that it is suggested to use ADDCOLUMNS instread of doing the calculation in the SUMMARIZE function.

     

    Example:

    SmallerBigTable =
ADDCOLUMNS (
    SUMMARIZE ( BigTable; BigTable[Type]; BigTable[Formula]; BigTable[Brand] );
    "Count of rows"; COUNTA ( BigTable[A] )
)

    Hope this helps!

    • hidenseek9's avatar
      hidenseek9
      Post Patron
      9 years ago

      Anonymous

       

      SmallerBigTable = 
SUMMARIZE(
	BigTable;
	BigTable[Type];
	BigTable[Formula];
	BigTable[Brand]
)

      Above formula works great, except for one problem.

       

      When I put above DAX, it brings all Types. How can I reformat the DAX expression

      so that it will extract just Type A?

       

      Many thanks,

       

      H

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        hidenseek9,

         

        Try this:

         

        SmallerBigTable = 
SUMMARIZE(
	FILTER(
		BigTable;
		BigTable[Type]="A"
		);
	BigTable[Type];
	BigTable[Formula];
	BigTable[Brand]
)