Measure vs TE3 Dax Query

DAX DAX Calculations
1

I’ve got a query I built in TE3 that returns a result. It’s hard-coded for testing purposes.

I’ve built a table in PB that is filtered to what the DAX Query (attached) is using. I want to build a measure (pasted in a code block below) that I can then compare against each row of the table.

What I was expecting when I placed the measure on the table is that the number would be repeated on each row.

My first step is simply displaying the value of the measure on the row. It returns a blank.

My second step, if it had worked, would be to build a measure that compares the Amounts - Source value to the “Unusual Items Value Filter” measure. If Amounts - Source is greater than “Unusual Items Value Filter”, return a 1, otherwise return a zero.

I would then put this new measure in as a filter and only show rows where the value is 1.
AVERAGEX with virtual table.pbix (3.7 MB)
DAX Query 1.dax (2.0 KB)

Unusual Item Filter Value = 
VAR selectedComparison = "Total Operating Revenue"
VAR selectedReportName = "260 Report Name"
VAR selectedMonthOffSet = 0
VAR selectedCaption = "Medical"
VAR selectedCompany =
    MINX( FILTER( 'structure', 'structure'[Report Name] = selectedReportName ), 'structure'[Reporting Company] )
VAR ActCompNum =
    CALCULATE(
        [MTD Source],
        KEEPFILTERS( cost_centers[Reporting Company] = selectedCompany ),
        KEEPFILTERS( calendar[Month Offset] = selectedMonthOffSet ),
        KEEPFILTERS( captions[Caption] = selectedComparison )
    )
VAR ActCompDen =
    CALCULATE(
        [MTD Source],
        KEEPFILTERS( cost_centers[Reporting Company] = selectedCompany ),
        KEEPFILTERS( calendar[Month Offset] = selectedMonthOffSet ),
        KEEPFILTERS( captions[Caption] = selectedCaption )
    )
VAR BudCompNum =
    CALCULATE(
        [MTD Budget],
        KEEPFILTERS( cost_centers[Reporting Company] = selectedCompany ),
        KEEPFILTERS( calendar[Month Offset] = selectedMonthOffSet ),
        KEEPFILTERS( captions[Caption] = selectedComparison )
    )
VAR BudCompDen =
    CALCULATE(
        [MTD Budget],
        KEEPFILTERS( cost_centers[Reporting Company] = selectedCompany ),
        KEEPFILTERS( calendar[Month Offset] = selectedMonthOffSet ),
        KEEPFILTERS( captions[Caption] = selectedCaption )
    )
VAR ActRatio = DIVIDE( ActCompNum, ActCompDen )
VAR BudRatio = DIVIDE( BudCompNum, BudCompDen )
VAR BudActRatio = DIVIDE( BudRatio, ActRatio )
   
VAR FV1 = 
    CALCULATETABLE(
        SELECTCOLUMNS(
            amounts,
            "CC", RELATED( cost_centers[Cost Center] ),
            "Acct",RELATED( chart_of_accounts[Account ID] ),
            "@WeightedAvgBud", [Amounts - Budget] * BudActRatio            
        ),
        'calendar'[Month Offset]=selectedMonthOffSet,
        cost_centers[Reporting Company]=selectedCompany,
        captions[Caption]=selectedCaption
    )
    
VAR FV2 = AVERAGEX(FV1,[@WeightedAvgBud])
RETURN
    FV2
2

I’m hoping the attached spreadsheet helps me get the point across.

The values are different, but the concept is the same.

I’ve ultimately got six choices I want to allow my users.
**Total Operating Revenue
**Total Operating Expenses
**Operating Income
**Inpatient Discharges
**Inpatient Days
**WRVUs

Right now, I’m testing Total Operating Revenue, but the concept is the same for all.

I start with the actual and budget for each and then determine the ratio of the actual to the budget. That value is then multiplied by the budget for the caption chosen by the user, in this case “Medical” (Column H, rows 14 thru 157). I then determine the average (cell H5) for H14:H157. The average is then compared to the Amounts - Source (Column C). If a column C cell is greater than the average, then I return TRUE, otherwise, I return FALSE (see Column N).

If the values in Column N are TRUE, then I display the row.
unusual-item-testing2.xlsx (52.4 KB)

3

Hi @kaDargo, while waiting for the response from our members and experts, have you tried watching the TE3 Tutorial vids by @AntrikshSharma to see if this scenario was discussed?

The course can be accessed here: Enterprise DNA

4

Thanks, I’ll take a look.

I’m discovering that there are no limits to my ignorance :slight_smile:

5

Hi @kaDargo - Look at your Power BI file, I don’t see any relationship setup b/w any tables like Cost_Centers, Caption, Calendar etc used in the measure. Also, the Visual only contains two Dimension fields while your logic is referring to many.

Please check your Data Model to check how could relationship be setup b/w different tables. And if you are planning to use Disconnected tables then check logic in your measures as current measure like “MTD Source” doesn’t makes much sense to me with reference to all those disconnected tables.

Thanks
Ankit J

6

Hi @ankit, thanks for your response.

Why would I build a relationship between my dimension tables? Or, did I misunderstand your statement?

7

Hi @kaDargo - Please ignore my previous statements, I didn’t check the whole relationships. Can you elaborate what’s exactly the issue you are facing, so I may be able to help.

Thanks
Ankit J

8

Hi @kaDargo, do you still need help with this issue? Do you mind providing @ankit with the details requested so he can better help you? Thanks!

9

@ankit ,
Apologies, I didn’t see your response.
I think I’ve figured out my Unusual Items Filter, although I think the code is unnecessarily redundant.

Now, I’m trying to count the results of the Unusual Items Filter. The number I’m getting is different that what I’m seeing on the page.

I’m attaching a new version of the pbix. Hopefully, you can see something I’m missing.
GL Insights.pbix (2.0 MB)

1 Like
10

Hi @kaDargo - I have looked into the file and found some basic reason why the result is different in “# of Unusual Items” and the visual.

In # of Unusual Items, condition applied is VAR Result = countrows( FILTER( GL_KEY, [Unusual Item Filter] = 1 ) )

This will Calculate Unusual Item Filter for GL_KEY table and return Rows where value is 1.

If we create a new Column in GL_KEY using Unusual Item Filter, then we can see all those records which are being returned with value 1. Also, if we individually check values for internal measures, we can see that for all records “Avg of Weighted Bud” is being returned as “Null” while when used in visual value for “Avg of Weighted Bud” is returned.

This seems to be causing the difference b/w the “# of Unusual Items” and the count of rows in Visual.

Why is “Avg of Weighted Bud” is null in GL_KEY table needs to be analyzed.

Check attached PBIX file with added Columns in “GL_KEY” table using existing measures.
GL Insights.pbix (2.0 MB)

Thanks
Ankit J

11

@ankit , you added the measure as a column on the table!

If I understand you correctly, this will help in testing the results of the measure on a row-by-row basis.

Thanks for the idea!

12

Hi @kaDargo - Yes, that’s the idea for doing the analysis for the issue.

Thanks
Ankit J

13

Hi @ankit ,
It appears when we add the Unusual Items Filter as a calculated column that it’s not including the subsys > AverageWeightedBudget comparison and instead is only returning a 1 if the subsys is greater than the budget.

14

Hi @kaDargo - This is as if you check the Value Column in GLKEY table that I added for analysis, AverageWeightedBudget (3rd Value) for all the Rows is coming as Blank. This is something needs to be analyzed why it is coming as Blank instead of actual value of 11338 as in visual.

Thanks
Ankit J

1 Like
15

Hi @ankit ,
That column is coming in blank because the Weighted Budget is blank. But it’s only blank when you add the measure as a column.

16

Hi @kaDargo! 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!