topic How to run multiple table generation commands at once? in DAX Commands and Tips

How to run multiple table generation commands at once?

Asish24 — Thu, 04 Apr 2024 13:55:18 GMT

I want to find the top 5 highest values of a column and generate an individual table for each value, like highest, 2nd highest, 3rd highest and so on....

As DAX doesn't have iteration functions, and multiple commands can't be executed in one go I am unable to do the above mentioned task.

I'm using the below mentioned command 5 times by changing the value of M from 1 to 5 to get the desired 5 tables, how to get them at once?

NewTable = TOPN(1, FILTER(ALL(TableName), RANKX(ALL(TableName) , TableName[ColumnName])= M),

TableName[ColumnName])

Re: How to run multiple table generation commands at once?

lbendlin — Thu, 04 Apr 2024 18:18:33 GMT

That's what GENERATE() is for.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: How to run multiple table generation commands at once?

Anonymous — Wed, 10 Apr 2024 05:53:04 GMT

Thanks lbendlin

Hi, Asish24

Do you mean to automatically generate 5 separate calculated tables? There are no similar batch commands in Power BI for the time being. As you mentioned, make 5 copies of the formula, using one copy in each calculation table. If you need a table similar to the Cartesian product, you can use the formula provided by lbendlin, which will be very portable to generate a Cartesian product table. Something like this is shown in the figure below:

table1:

table2:

Use GENERATE():

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.