DAX Importing Measures from tabular server into table

[th21]

I am new to DAX, and I am trying to build a query for a report. I have figured out a lot by myself, but I am stuck on this current problem.

I am connecting to a Tabular Data Server which lists "Sales Dollars" and "Sales Dollars LY" as Measures (1.2 Double).

I want to know the best way to load Measure into a table that also includes several Non-Measures (i.e. Text and Numbers such as 'Table'[Value]) and a ton of Filters (some of which have multiple criteria)?

Here is the code I am currently using. It runs, but it doesn't give me everything I am looking for. See comments in the code. I am open to using a different method (i.e. SUMMARIZECOLUMNS) as long as it works!

EVALUATE
	CALCULATETABLE (
		ROW (
		// I would like to list each 'Calendar'[Month Name/Year] i.e. "January 2023"
			"Sales $ TY", [Sales Dollars],
			"Sales $ LY", [Sales Dollars LY]
		),
		'Calendar'[Month Name/Year] IN {"January 2023", "February 2023", "March 2023", "April 2023", "May 2023", "June 2023"},
		'Calendar'[Year Number] = 2023,
		'Transaction Category'[Transaction Category] = "Wholesale",
		'Transaction Type'[Transaction Group] = "Group C",
	// Line below also needs to filter by:    'Transaction Type'[TT] = "Type D"
		'Transaction Type'[TT] = "Type A"
	)

[th21]

Thank you for your response.

I have revised what I would like the table to look like so you can better understand:

Year	Total Sales (Jan - Jun)
2023 (TY)	3875
2022 (LY)	4185

Ideally, I would also like an expanded version of it showing month by month:

Year	Jan Sales	Feb Sales	Mar Sales	Apr Sales	May Sales	Jun Sales	Total
2023 (TY)	500	450	600	700	725	900	3875
2022 (LY)	550	425	610	750	750	1100	4185

More background:

This is all coming from one main table via one tabular data connection. There is no PBIX file as of yet, I am conceptually constructing this in DAX Studio to check for errors and once I have a solution I will begin building visuals and a data model in Power BI. This is one element that is ultimately going to be part of a larger report that is currently 100% Excel driven.

Measures:

This has been my main obstacle. I don't understand why these measures are loading so differently than the other table values.  I have spent hours of reading documentation and searching forums.

Measure Name	Type	Description
[Sales Dollars]	1.2 Double	returns sales with or without a year parameter
[Sales Dollars LY]	1.2 Double	reuturns sales dollars ONLY when a 'Calendar' field with a year is input i.e. 'Calendar'[Year Number] = 2023 wil return 2022 sales and 'Calendar'[Month Name/Year] = "January 2023" will return Jan '22 sales.

Filters:

Name	Type	Description
'Calendar'[Year Number]	Int	Returns year i.e. 2023
'Calendar'[Month Name/Year]	Text	Returns Month/Year i.e. "January 2023"
'Transaction Category'[Transaction Category]	Text	Returns a category i.e. "Wholesale"
'Transaction Type'[Transaction Group]	Text	Returns a type of transaction i.e. "Direct to Customer"
'Transaction Type'[TT]	Text	Returns a subtype of transaction i.e. "Palletized" or "Shipping Container"

[v-yiruan-msft]

Hi @th21 ,

According to your description and formula, it seems that you want to create/get a table base on two measures. And as checked the formula, it involves multiple tables('Transaction Category', 'Transaction Type' and 'Transaction xx' etc.). Is there any relationship among them? Could you please provide more raw data in your table 'Project Role Price' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards