Add Count to Datatable (passed as variable)

DAX DAX Calculations
1

I’m currently using Power Automate to pass on dax measures to a dataset and have the evaluated result returned. This works great for passing on scalar values as parameters for the calculation. Given the limitation of only being able to do 120 calculations per minute, I wish to pass on a full table to be evaluated and return the results so it’s just one call.

  • so very important to note is that the values being passed on to the dataset will be a table which will be virtual.

So let’s assume this is an existing table in the model:

Filter X Filter Y Filter Z
A M P
B N Q
C O S
D A T
E B U

and in my measure being passed on, I’m trying to figure out the occurence of the following combination

var ToBeCheckedDatatable =
DATATABLE(
        "Pass on X",STRING,
        "Pass on Y",STRING,
        "Pass on Z",STRING,
        {
            {"A","M","P"},
            {"B","N","U"},
            {"E","B","U"}
        }
    )

edna - datatable evaluation.pbix (17.5 KB)

So the result should be something like:

Pass on X Pass on Y Pass on Z Occurrence
A M P 1
B N U 0
E B U 1

I was thinking about doing something like:

var result = 
ADDCOLUMNS(ToBeCheckedDatatable,
countrows(treatas(summarizecolumns(Filter X,Filter Y, Filter Z),Pass on X,Pass on Y,Pass on Z))

But I cannot reference the Pass on X,Pass on Y,Pass on Z because they are columns of a table created in a variable.

Any assistance would be more than welcome!

2

@Wlknsn edna - datatable evaluation.pbix (21.4 KB)

image
image911×800 57.3 KB

image
image1148×728 49.8 KB

image
image1521×671 46.5 KB

3

The virtual table “CountOfOccurenceFilteredbyDatatable” is not a table in the model. I can only pass it on as a variable hence I cannot reference its column so not sure if any of the above works.

4

Your requirement isn’t clear, use the code I have shared play with it and try to implement in your scenario.

5

That’s the point. Just add the datatable as a variable in your code and you can’t reference it. So i can’t play around with your code.

What you did was create a table first and then add measures (this is I can do too), the problem is that I have to pass that data as a variable within a measure.

I know “filter” can reference a column from a table in a variable but can’t make that work to calculate the occurrences in another table.

6

Added to the title also “passed as variable”

7

Nope, adding the datatable to any of those measures and referencing doesn’t work at all. All of the above is when the datatable is made part of the model first, which is not the ask as pointed out in the introduction and provided in the sample powerbi where this was part of the measure already.

8

Hi @Wlknsn! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!