Dynamic Axis Using field Paramters !

AlanP514 · ‎02-13-2023

I have three tables

Dim_calendar and Sheet1 and for selection purposes I created one field parameters slicer

I have a slicer which I created using field parameters whose values are WOW & MOM

If I click WOW dynamic axis should change and values should show for these three measures in one chart with a weak comparison

1 Views

2 Orders

3 CVR
These are measures I need to showcase measures with WOW

if i click mom i need to see the same measures with Month comparison

1 Views

2 Orders

3 CVR

Here I am attaching the pbix also so I am attaching the data and measures and calendar table dax which i am using

# CVR = SUM(Sheet1[CVR])

# Orders = SUM(Sheet1[Orders])

# Views = SUM(Sheet1[Views])

Start _Date	Views	Orders	CVR
1/1/2021	629	474	1703
1/2/2021	963	464	1670
1/3/2021	476	151	1735
1/4/2021	24	343	483
1/5/2021	662	500	4052
1/6/2021	1725	476	334
1/7/2021	638	54	125
1/8/2021	737	415	1650
1/9/2021	443	328	3581
1/10/2021	1007	454	3604
1/11/2021	366	478	1706
1/12/2021	678	242	820
1/13/2021	1895	288	4214
1/14/2021	1485	198	1661
1/15/2021	1948	69	3163
1/16/2021	1724	393	221
1/17/2021	1893	69	3960
1/18/2021	854	46	1962
1/19/2021	1448	319	779
1/20/2021	1259	76	941
1/21/2021	1170	486	2546
1/22/2021	93	250	2804
1/23/2021	89	30	2029
1/24/2021	1614	93	3556
1/25/2021	1078	282	2968
1/26/2021	1128	344	573
1/27/2021	1060	431	1832
1/28/2021	980	338	2654
1/29/2021	1286	381	3364
1/30/2021	462	445	2562
1/31/2021	1759	374	2474
2/1/2021	587	425	2906
2/2/2021	519	477	2349
2/3/2021	1907	398	3013
2/4/2021	1964	343	4356
2/5/2021	1843	464	256
2/6/2021	1257	395	1364
2/7/2021	53	405	1287
2/8/2021	763	484	4237
2/9/2021	155	394	2145
2/10/2021	1955	238	4674
2/11/2021	367	234	2732
2/12/2021	1671	405	1711
2/13/2021	1133	378	536
2/14/2021	1865	496	364
2/15/2021	1205	78	2286
2/16/2021	1191	210	3582
2/17/2021	362	184	4684
2/18/2021	125	250	2601
2/19/2021	1133	207	2693
2/20/2021	1070	388	3468
2/21/2021	1886	368	3658
2/22/2021	623	128	3254
2/23/2021	64	332	1538
2/24/2021	836	276	936
2/25/2021	1920	241	4484
2/26/2021	1201	409	60
2/27/2021	654	257	2416
2/28/2021	459	214	3624
3/1/2021	1710	38	377
3/2/2021	1026	386	3467
3/3/2021	311	219	4892
3/4/2021	1572	270	4460
3/5/2021	1178	220	3001
3/6/2021	1988	322	896
3/7/2021	1688	100	2146
3/8/2021	639	378	1974
3/9/2021	184	60	4187
3/10/2021	732	63	2181
3/11/2021	1201	55	220
3/12/2021	657	106	1234
3/13/2021	1330	451	4994
3/14/2021	1486	234	480
3/15/2021	573	275	4740
3/16/2021	45	323	4589
3/17/2021	328	221	1526
3/18/2021	400	70	3697
3/19/2021	590	383	1945
3/20/2021	1090	277	3480
3/21/2021	618	94	1379
3/22/2021	827	226	3148
3/23/2021	663	426	3659
3/24/2021	1917	367	1959
3/25/2021	928	463	1179
3/26/2021	459	141	2242
3/27/2021	338	155	1876
3/28/2021	1223	213	3671
3/29/2021	555	133	2033
3/30/2021	36	414	2053
3/31/2021	1782	418	4881
4/1/2021	175	266	2283
4/2/2021	598	379	3639
4/3/2021	1784	412	3629
4/4/2021	1485	405	1456
4/5/2021	544	57	4764
4/6/2021	935	391	1268
4/7/2021	1811	490	3485
4/8/2021	1001	262	4090
4/9/2021	1563	457	4624

Dim_Calendar =

var _FromDate= MIN(Sheet1[Start _Date])

var _ToDate= MAX(Sheet1[Start _Date])

var _Today=TODAY()

return

ADDCOLUMNS(

CALENDAR(_FromDate,_ToDate)

,"Year",YEAR([Date])

,"Year Start Date",DATE( YEAR([Date]),1,1)

,"Year End Date",DATE( YEAR([Date]),12,31)

,"Quarter",QUARTER([Date])

,"Quarter Name","Q"&FORMAT([Date],"Q")

,"Quarter Start Date",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1)

,"Quarter End Date",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0)

,"Year Quarter Number",COMBINEVALUES("-",YEAR([Date]),FORMAT( QUARTER([Date]),"00"))

,"Month",MONTH([Date])

,"Month Name",FORMAT([Date],"MMMM")

,"Month Name Short",FORMAT([Date],"MMM")

,"Month Start Date",DATE( YEAR([Date]), MONTH([Date]), 1)

,"Month End Date",EOMONTH([Date],0)

,"Year Month Number",FORMAT([Date],"YYYY-MM")

,"Year Month Name",FORMAT([Date],"YYYY-MMM")

,"Week of Year",WEEKNUM([Date])

,"Week Start Date", [Date]-WEEKDAY([Date])+1

,"Week End Date",[Date]+7-WEEKDAY([Date])

,"Year Week Number", COMBINEVALUES("-",YEAR([Date]),FORMAT( WEEKNUM([Date]),"00"))

,"Day",DAY([Date])

,"Day Name",FORMAT([Date],"DDDD")

,"Day Name Short",FORMAT([Date],"DDD")

,"Day of Week",WEEKDAY([Date])

,"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1

,"Day Offset",DATEDIFF(_today,[Date],DAY)

,"Month Offset",DATEDIFF(_today,[Date],MONTH)

,"Quarter Offset",DATEDIFF(_today,[Date],QUARTER)

,"Year Offset",DATEDIFF(_today,[Date],YEAR)

)

jaweher899 · ‎02-14-2023

It sounds like you want to create a chart in Power BI that shows different measures based on a slicer selection. Here are the steps to create the chart:

Create a measure for each of the three measures you want to display, using the DAX formulas you provided (i.e. SUM(Sheet1[CVR]), SUM(Sheet1[Orders]), and SUM(Sheet1[Views])).
Create a new measure that will dynamically switch between the "WOW" and "MOM" comparisons based on the slicer selection. Here's an example of what that measure might look like:

Dynamic Measure =
IF (
SELECTEDVALUE ( Dim_calendar[Parameter Slicer] ) = "WOW",
[WOW Measure],
[MOM Measure]
)

Replace [WOW Measure] and [MOM Measure] with the measures you created in step 1. 3. Create a column chart visualization.

Drag the "Start_Date" field from the "Sheet1" table to the "Axis" well of the chart.
Drag the "Dynamic Measure" field to the "Values" well of the chart.
Customize the chart to your liking (e.g. adjust the colors, title, and axis labels).

When you select "WOW" in the slicer, the chart should display the "Views", "Orders", and "CVR" measures with a week-over-week comparison. When you select "MOM" in the slicer, the chart should display the same measures with a month-over-month comparison.