Dax query in power automate flow - date format

Power Automate
1

Hello Forum,
I’m looking for a bit of assistance in power automate flow which utilizes a DAX query I pull from the performance analyzer in PowerBI.
Here’s the query:

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		FILTER(
			KEEPFILTERS(VALUES('Funding Sources'[Funding Source])),
			NOT('Funding Sources'[Funding Source] IN {BLANK()})
		)

	VAR __DS0FilterTable2 = 
		FILTER(
			KEEPFILTERS(VALUES('UC FY21-Current'[Visit Date])),
			AND(
				'UC FY21-Current'[Visit Date] > EOMONTH(TODAY(), -2),
				'UC FY21-Current'[Visit Date] <= EOMONTH(TODAY(),-1)
			)
		)

	VAR __DS0FilterTable3 = 
		TREATAS({"Liz Carter",
			"NOC Re-Entry"}, 'UC FY21-Current'[Conference])

	VAR __DS0FilterTable4 = 
		TREATAS({"Rent",
			"Util"}, 'UC FY21-Current'[Rent/Util])

	VAR __DS0Core = 
		SELECTCOLUMNS(
			KEEPFILTERS(
				FILTER(
					KEEPFILTERS(
						SUMMARIZECOLUMNS(
							'Funding Sources'[Funding Source],
							'UC FY21-Current'[HoH Name],
							'UC FY21-Current'[Caseworker],
							'UC FY21-Current'[Case #],
							'UC FY21-Current'[Visit Date],
							__DS0FilterTable,
							__DS0FilterTable2,
							__DS0FilterTable3,
							__DS0FilterTable4,
							"CountRowsUC_FY21_Current", CALCULATE(COUNTROWS('UC FY21-Current'))
						)
					),
					OR(
						OR(
							OR(
								OR(
									NOT(ISBLANK('Funding Sources'[Funding Source])),
									NOT(ISBLANK('UC FY21-Current'[HoH Name]))
								),
								NOT(ISBLANK('UC FY21-Current'[Caseworker]))
							),
							NOT(ISBLANK('UC FY21-Current'[Case #]))
						),
						NOT(ISBLANK('UC FY21-Current'[Visit Date]))
					)
				)
			),
			"'Funding Sources'[Funding Source]", 'Funding Sources'[Funding Source],
			"'UC FY21-Current'[HoH Name]", 'UC FY21-Current'[HoH Name],
			"'UC FY21-Current'[Caseworker]", 'UC FY21-Current'[Caseworker],
			"'UC FY21-Current'[Case #]", 'UC FY21-Current'[Case #],
			"'UC FY21-Current'[Visit Date]", 'UC FY21-Current'[Visit Date]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			501,
			__DS0Core,
			'Funding Sources'[Funding Source],
			1,
			'UC FY21-Current'[HoH Name],
			1,
			'UC FY21-Current'[Caseworker],
			1,
			'UC FY21-Current'[Case #],
			1,
			'UC FY21-Current'[Visit Date],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'Funding Sources'[Funding Source],
	'UC FY21-Current'[HoH Name],
	'UC FY21-Current'[Caseworker],
	'UC FY21-Current'[Case #],
	'UC FY21-Current'[Visit Date]

The issue I have is that when the “Visit Date” is displayed in the .csv file it is showing up with the time as well as the date:

{
    "Funding Sources[Funding Source]": "EPI",
    "UC FY21-Current[HoH Name]": "baskerville, yahadah",
    "UC FY21-Current[Caseworker]": "Bouquet, Karen",
    "UC FY21-Current[Case #]": "2023-57439",
    "UC FY21-Current[Visit Date]": "2023-08-11T00:00:00"
  },

What do I need to change in the query to ensure that it comes out, for example above, as “2023-08-11” ? The data is stored in the BI model as Short Date and displays properly in the BI Visual.

thanks much for any assistance you can provide.

2

Hi @jwesselsvdp

I’m wondering if the Dynamic Format function would work

Here is the link to the Dax Workout 1.
youtube video done by Curbal
DAX Workout 001 - Dynamic Format Strings - Workouts / DAX - Enterprise DNA Forum

thanks
Keith

3

I don’t think that will work as my BI visuals look fine. Problem is in the power automate flow where I pull the DAX query from BI project file on a visual and use that as the “Run a query against a dataset” step in Automate which generates the output .csv file as noted in the sample output for "UC FY21-Current[Visit Date]. Sincerely appreciate the quick look/see though.

4

I think power automate can handle the date formatting thorough dax programming

5

Agree, just wondering exactly how to do that within the query itself.

6

when I researching that issue. there are some youtube videos that you can review