Display data from two tables in a Matrix visual

[JustinDoh1]

Hi Community,

I have posted my Pbix file here.

I am trying to display data from two datasets in a Matrix visual.

There is a relationship between two datasets (Actual and Estimate).

Here is a little demo of what I am trying to do.

Currently, the first Matrix are composed of two Matrix.

There is a relationship between these two tables using a dim table (BudgetType).

A critical point is two tables have a measure showing as "Actual" or "Estimate".

When this measure is applied into Stacked column chart, it works.

The error on Matrix is this:

These are columns for Stacked Column chart, and it works.

These are columns for Matrix, and I am not sure where/how logic for "ActualOrEstimate" could be applied.

The expected output is having one Matrix displaying both Actual and Estimated columns.

Thanks.

[davehus · Accepted solution]

Hi @JustinDoh1 , Me again.. 🙂 Change Values to firstnonblank as below.

[JustinDoh1]

@davehus 

I have two questions regards to using FirstNonBlank.

It appears that this function is fairly new one (if I am not mistaken).

1) What is usage of this function?

2) What does "1" mean for the expression?

Thanks.

[davehus]

Hi @JustinDoh1 , It's used the return the first non blank value in a table. You would typically use it with a measure. So for example if you had a sales table with null values and you wanted to get the first date where there is a value, you could write. FIRSTNONBLANK(Sales[Date],[Sales Measure])

The 1 is a way of short circuiting the measure for want of a better word. So when you add the 1 instead of a measure, you are basically asking for the first text value it finds.

So if the first row that is not blank and equals Actual then do this else do that.

If you just write FirstNonBlank(Actual[ActualOrEstimated2],1) into a measure and drag it into a visual, you will see what it's doing.

HTH,

D

[JustinDoh1]

@davehus  Looking back to your previous response, I realized that you mentioned about "FirstNonBlank" on the post, but the Pbix file had the "Value".

Sorry. I might bypassed the details and just jumped into the Pbix file you have shared.

Anyway, I am learning new stuffs. Thanks!

[JustinDoh1]

@davehus 

You Rock!

Thank you so much!

How do you know all this stuffs?