Could I have some examples to help me learn DAX?

[Dan_at_TWE]

I'm looking to see some examples of DAX syntax for the following situations to examine. I would like to compare the difference in DAX syntax between picking a value from a speadsheet table and picking a value from a range. (aka. just a speadsheet, no Excel tables).

1) A basic calulation with operands from 2 different Excel tables.

2) A basic calculation with one operand from an Excel table and another from a range.

3) A basic calculation with both operands from ranges, no tables.

[PhilipTreacy]

@Dan_at_TWE 

Can't show you examples of something that DAX can't do.

[Dan_at_TWE]

Ok. Now we're getting somewhere...

What I think you are telling me is, if I want DAX to grab CALCULATE operands from two different sources, each range speadsheet source must be converted an actual Excel table using Excel first. Correct?  

[PhilipTreacy]

@Dan_at_TWE 

That would probably be the easiest way to get the data into PBI, but not the only way

Power BI Desktop can 'see' an Excel table and load it into Power Query and then the data model.

Data in Excel that is just sitting in a range (and not in a table) can't be 'seen' by PBI n the same way that an Excel table can, but it can be loaded into PBI if you load the entire sheet, which isn't always what you want to do.

So, converting any ranges to Excel tables is definitely the best way to go.

Phil

[Dan_at_TWE]

Thanks, Phil.

[lbendlin]

Power query only knows Excel sheets or named tables. It can not address ranges. 

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com/?forum=2d80fd4a-16cb-4189-896b-e0dac5e08b41

[Dan_at_TWE]

Just to clarify, what I mean by a "range"... An Excel spreadsheet that has not been converted to a table. That's what Excel calls in anyway.

Not a range of values within a particular spreadsheet.

[lbendlin]

That is different.  Excel distinguishes between tables, named ranges, and sheets.  Power Query only recognizes tables and sheets.

[Dan_at_TWE]

I'm using the terminology from the Excel interface itself.   When you "untable" an Excel table, the Excel control you use is "Convert to Range" under the Table Design tab.   

Sorry if I've confused anyone.   I thouight using the terminology actually in the Excel interface was the proper thing to do. When I was saying "range", I was refering to a speadsheet with no tables.

I guess the proper phasing would be, I want to see an

• example of a calculation using values from two seperate sheets
• and another calculation example of using two values, one value from a table and the other from a sheet

For simplicity, assume all the tables or sheets are in the same workbook.

[lbendlin]

ok, that's something we can work with.

In each case you need to separate Power Query source queries, one for each sheet or table.  Then you can merge or reference them with each other as needed.

[Dan_at_TWE]

So, I have to use power query in Excel to prep the data first before it is brought into Power BI? Now I'm really confused. I'm not getting something about the concept of a basic calculation/measure.

I have two sheets here.   

One with time records: Date,Name,Hours worked for that day.

One with Rates:  Name, Hourly Rate of pay

I have a one to many relationship, of course, based on the name.  All I want to do is:

Hourly Rate x Hours Worked for that day = Pay for that day for that person

And be able to put the result someplace.  I feel really stupid that I can't figure out how to do a simple multiplication with numbers from 2 sources. I know there is a way to say, "Take these 2 numbers and put their product here".

[PhilipTreacy · Accepted solution]

Hi @Dan_at_TWE 

Download PBIX file with example below

I've made up some data but used the table names in your image.

The DAX measure to calculate each person's pay is

Pay = MAX('Billing Rate'[Hourly Rate]) * MAX('Timecards'[Hours Worked])

You then display the columns you want and the measure in a table.

Regards

Phil

[Dan_at_TWE]

Thanks Phil, I'll look this over.

I tried this:

Total Pay = SUMX(Timecards,Timecards[Hours]*RELATED(Rates[Rate]))

and it also worked.  I have to examine both very carefully and figure out what each is doing. And which is appropriate for what I am trying to do.

[PhilipTreacy]

@Dan_at_TWE 

More than one way to skin a cat ............

[Dan_at_TWE]

Being a pessimist, I'm assuming there are pitfalls for each method. Depending on what you want, you have to use one or the other. I have to learn more DAX to figure that out.   I was blocked in going further until I got this part down.

[PhilipTreacy]

Hi @Dan_at_TWE 

DAX doesn't work that way.  Data is loaded into PBI and manipulated with Power Query (M Language) to create tables.

Such tables are Power BI specific, they are not the same as Excel tables.

Data stored in an Excel table or an Excel range is not distinguishable once its inside PBI.

Having loaded data into PBI, and used Power Query to transform in whatever way you wish, the data is then loaded into the PBI Data Model as a (Power BI) table.

It's at this point that DAX can access the data.  As I said, DAX doesn't know where the data came from and has no way to know, so your questions have no answers because the syntax used in DAX to manipulate data is the same regardless of the data source.

Check out these sites for all you need to know on DAX

Home - SQLBI

Data Analysis Expressions (DAX) Reference - DAX | Microsoft Learn

Regards

Phil

[Dan_at_TWE]

Thanks anyway. I thought someone here could show me at leat one example.