How to view a column returned by a measure

[Loubot3000]

I want to be able to view columns returned by measures.

For example, I have this measure:

Test 3 =

VAR currMonth =

    MAX(Sheet1[Month ID])

VAR currPeriod =

    FILTER(

        ALL(Sheet1[Month ID]),

        Sheet1[Month ID] <= currMonth

    )

RETURN currPeriod

But I get the following error whjen I put this in a table:

How do I view the values in the column returned by this measure?

Thanks 🙂

[PaulDBrown]

Wrap the RETURN expression in COUNTROWS, add the MONTH ID to a visual, and add the measure to the filter pane and set the value to greater or equal to 1

[Loubot3000]

I think this solution isn't so good because:
a) it returned a single value because the visualization is filtered to one month. The measure relies on that being the case in order to calculate MAX, however, I want to return the entire column that is produced, unfiltered.
b) it isn't really a general solution. I would need to do something different for each measure in order to view it in that sort of way.

I just want to see the entire column that FILTER() returns. Or more generally I want to be able to view anything returned by anything, so that I can actually debug my code. Otherwise how tf are people debugging DAX in Power BI?

[PaulDBrown · Accepted solution]

Here is an example of how you can get the values of a filtered table using a measure, as I suggested in my previous post:

Filter months until current month =
COUNTROWS ( FILTER ( 'Table', 'Table'[Month] <= MONTH ( TODAY () ) ) )

Or you can use an external tool like DAX Studio to run queries:

https://daxstudio.org/ 

https://daxstudio.org/docs/tutorials/writing-dax-queries/ 

Using DAX Studio in my example:

Or, since your request is actually a table expression (not a measure) you can test the results by creating a physical table (not recomended since you will need to delete each of the test tables as you go along)

.

[Loubot3000]

That last one is what I was looking for! Thank you.

The first one doesn't work for me still because it would change every time depending on what I'm trying to show. For example, if I wanted to show ALLSELECTED() months rather than ALL() months (so excluding missing months in a given context), this 'More than month 1' filter method would not work.

Second one isn't ideal because I have everything set up in Power BI.

[PaulDBrown]

I would use DAX Studio. Once installed, you can launch it directly from the Desktop and it will be connected to your model. It integrates seamlessly and you can query all the data in the model, as well as doing other things. It is a very usefull tool and the best part is it's free...

[confidentnwrong]

You should only use measure to return a single value, not to get a list / column of them.

Try using a calculated column instead ("Modeling" at the top of the window => "New column")

[Kishore_KVN]

Hello @Loubot3000 , there are multiple errors in the screenshot that you pasted. 

1. Measure you pasted is Test and error is with Test3 so check it properly first. 

2. Your measure is not calculating anything its just filtering data so that will not help you to get any answer and it will reflect same error of multiple values. So please correct your measure. If you are not clear in that, please tell us your requirement and we will help you in writing that measure. 

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

[Loubot3000]

Apologies, I renamed it after copying the code into here, but before I took a screenshot of the error. But yeah that's definitely not what's causing the issue.

The measure returns a column - how do I view this column?

In this case, the measure returns a column of all the Month ID's (numbers associated with each month) that are less than or equal to the current max (within the given context, i.e. the filters on the slide).
I imagine I need to return a specific kind of variable that the visualizations know how to handle.

How do I do this?

[Kishore_KVN]

In that case drag that measure into a tabe visual and also pull all other attributes as well into the table visual to see the data as a column. 

[Loubot3000]

This gives the error I showed above.