topic Re: How do I populate my result column names dynamically in DAX Query? in DAX Commands and Tips

How do I populate my result column names dynamically in DAX Query?

KoppulaPSR — Wed, 03 Jul 2024 12:24:51 GMT

Dear experts,

I have requirment to populate column names dynamically using DAX query.

Use case:-

Using below DAX Query i am fetching Product Color wise sales for selected day as well as for previous day, presently i have managed those measure names as "SelectedDaySales" , "PreviousDaySales". However, my requirment is instea of Selected & Previous daysales, need to replace with dates as measure names. It will be like below mentioned screenshot. Is it possible? Normally in MDX we can use "MemberValue" property and achive the requirment. Not sure is there any way we can do in DAX.

Please advise.

EVALUATE VAR _ProductCat = distinct ('Product'[Color]) VAR _Time= SELECTCOLUMNS(FILTER('Date','Date'[Date]= DATE(2013,07,02)),"Day",'Date'[Date]) VAR _ColorWiseSales = ADDCOLUMNS( CROSSJOIN(_ProductCat,_Time), "SelectedDaySales",'Internet Sales'[Drvd_Internet_Total_Sales], "PreviousDaySales",CALCULATE('Internet Sales'[Drvd_Internet_Total_Sales], PREVIOUSDAY('Date'[Date]))) VAR Result = FILTER(_ColorWiseSales, not isblank('Internet Sales'[Drvd_Internet_Total_Sales]) && 'Product'[Color]<>"NA") RETURN Result

Re: How do I populate my result column names dynamically in DAX Query?

Anonymous — Thu, 04 Jul 2024 05:51:51 GMT

Hi KoppulaPSR ,

You can follow the steps below to get it in Power BI:

1. Create a measure below

Measure = VAR _color = SELECTEDVALUE ( 'Product'[Color] ) VAR _date = DATE ( 2013, 07, 02 ) VAR _seldate = SELECTEDVALUE ( 'Date'[Date] ) VAR _pids = CALCULATETABLE ( VALUES ( 'Product'[productid] ), FILTER ( 'Product', 'Product'[Color] = _color && 'Product'[Color]<>"NA" ) ) VAR sales = SUMX ( FILTER ( ALLSELECTED ( 'Internet Sales' ), 'Internet Sales'[Date] = _seldate && 'Internet Sales'[productid] IN _pids ), [Drvd_Internet_Total_Sales] ) RETURN IF ( _seldate IN { _date - 1, _date }, sales , BLANK () )

2. Create a matrix visual( Rows: 'Product'[Color] Columns: 'Date'[Date] Values: [Measure])

Best Regards

Re: How do I populate my result column names dynamically in DAX Query?

KoppulaPSR — Thu, 04 Jul 2024 07:01:40 GMT

Anonymous

Thank you for the idea, in my case need to handle the use case in Tabular Cube under "Detailed Row expression".

Any inputs would be highly appreciated.

Re: How do I populate my result column names dynamically in DAX Query?

Anonymous — Fri, 05 Jul 2024 08:20:21 GMT

Hi KoppulaPSR ,

Do you mean the one in below screenshot? In that case, I'm afraid I can't fulfill your needs...

use case in Tabular Cube under "Detailed Row expression".

Best Regards

Re: How do I populate my result column names dynamically in DAX Query?

KoppulaPSR — Fri, 05 Jul 2024 09:14:01 GMT

Exactly need to handle in Tabular Cube, the problem is now we are migrating from Multi-Dimensional Cube to Tabular Cube and planning to host Tabular Cube in Azure Cloud under PaaS model.
Since the use case already working in MOLAP Cube, client is expecting as it is in Tabular Cube. Not sure whether MS planning to implement this kind of functionality in future.