Notifications
Clear all
Power BI
4
Posts
2
Users
0
Reactions
90
Views
Topic starter
Hello Mynda!
I know you talked about the variable code name for slicers in your Power BI class but I cannot for the life of me find that topic again!
Where do I go within Power BI to find the variable name for the slicer so that I can refer to it in DAX code?
THank you!
Valerie
Posted : 27/08/2022 5:47 pm
Hi Valerie,
I know I cover this in the Power Pivot course in the CUBE functions lessons for Excel. In Power BI a Slicer is just another filter, so if the visual is filtered then most measures will pick up the filter state of the Slicer implicitly, unless you explicitly tell the measure to ignore filters by using the ALL function.
What was it you were wanting to do?
Mynda
Posted : 29/08/2022 12:57 am
Topic starter
Hi Mynda!
Sorry I'm late in getting back with you.
I was able to figure out how to use a form of a parameter within a measure. Yes, I do use 'implicit' filters but i also needed to create 'S' curves for an entire dataset while specifically filtering for particular values. Here's what I came up with! I had to create measures to retrieve the data value I was looking for and then assign that value to a VAR within the measure:
Pct_Planned_Baselay_ECDs =
VAR BaselaySubCategory = [SelectedSubCategory] (measure to retrieve a subCategory)
VAR SelectedAsset = [SelectedAsset] (measure to retrieve the selected Asset of 'REL' or 'Effort')
// Originally set to ALLSELECTED and then ALLCROSSFILTERED, and now ALLEXCEPT
VAR PLANNED = CALCULATE([WeeklyProgressSum],ALLEXCEPT(ProgressMini,ProgressMini[Asset],ProgressMini[Construction SubCategory]),ProgressMini[Week Ending]<=MAX(ProgressMini[Week Ending]),ProgressMini[Asset]=SelectedAsset,ProgressMini[Construction SubCategory]=BaselaySubCategory,ProgressMini[SeriesType]="Plan")
RETURN PLANNED
This code would create the data points necessary for a 'planned' s-curve.
I also have two other measures for 'Actual' and 'Forecast' s-curves:
Actual: (generates the data points necessary for the 'Actual' s-curve for the line visual)
Pct_Actual_Baselay_ECDs =
VAR BaselaySubCategory = [SelectedSubCategory]
VAR SelectedAsset = [SelectedAsset]
// measure to create percentages for actual progress line chart
VAR A1 =
// total up to a particular data for actuals only - Originally set to ALLSELECTED
CALCULATE([WeeklyProgressSum],ALLEXCEPT(ProgressMini,ProgressMini[Asset],ProgressMini[Construction SubCategory]), ProgressMini[Week Ending]<=MAX(ProgressMini[Week Ending]),ProgressMini[Asset]=SelectedAsset,ProgressMini[SeriesType]="Act", ProgressMini[Construction SubCategory]=BaselaySubCategory)
VAR A =
// total to date for actuals and forecast
CALCULATE([WeeklyProgressSum], ALLEXCEPT(ProgressMini,ProgressMini[Asset],ProgressMini[Construction SubCategory]), ProgressMini[Week Ending]<=MAX(ProgressMini[Week Ending]), ProgressMini[Asset]=SelectedAsset,ProgressMini[Construction SubCategory]=BaselaySubCategory,OR(ProgressMini[SeriesType]="Act",ProgressMini[SeriesType]="Fcst"))
VAR B =
// complete total of everything - doesn't include today or prior but it's the complete total of everything across the board
CALCULATE([WeeklyProgressSum], ALLEXCEPT(ProgressMini,ProgressMini[Asset],ProgressMini[Construction SubCategory]), ProgressMini[Asset]=SelectedAsset,ProgressMini[Construction SubCategory]=BaselaySubCategory, OR(ProgressMini[SeriesType]="Act",ProgressMini[SeriesType]="Fcst"))
VAR D = IF(A<=A1,A/B,"")
// allows line to stop once no data values are available - want to stop it at current date
RETURN D
Forecast: (generates the data points necessary for the 'Forecast' s-curve for the line visual)
Pct_Forecast_Baselay_ECDs =
VAR BaselaySubCategory = [SelectedSubCategory]
VAR SelectedAsset = [SelectedAsset]
// measure to create percentages for actual progress line chart
VAR A2 =
// total up to a particular data for actuals only - does not check on date? - Originally set to ALLSELECTED, and then ALLCROSSFILTERED
CALCULATE([WeeklyProgressSum],ALLEXCEPT(ProgressMini,ProgressMini[Asset],ProgressMini[Construction SubCategory]),ProgressMini[Asset]=SelectedAsset,ProgressMini[Construction SubCategory]=BaselaySubCategory,ProgressMini[SeriesType]="Act")
VAR A =
// total to date for actuals and forecast
CALCULATE([WeeklyProgressSum],ALLEXCEPT(ProgressMini,ProgressMini[Asset],ProgressMini[Construction SubCategory]),ProgressMini[Week Ending]<=MAX(ProgressMini[Week Ending]),ProgressMini[Asset]=SelectedAsset,ProgressMini[Construction SubCategory]=BaselaySubCategory,OR(ProgressMini[SeriesType]="Act",ProgressMini[SeriesType]="Fcst"))
VAR B =
// complete total of everything - doesn't include today or prior but it's the complete total of everything across the board
CALCULATE([WeeklyProgressSum],ALLEXCEPT(ProgressMini,ProgressMini[Asset],ProgressMini[Construction SubCategory]),ProgressMini[Asset]=SelectedAsset,ProgressMini[Construction SubCategory]=BaselaySubCategory,OR(ProgressMini[SeriesType]="Act",ProgressMini[SeriesType]="Fcst"))
VAR D = IF(A>=A2,A/B,"")
// allows line to stop once no data values are available - want to stop it at current date
RETURN D
And all the s-curve planned, actual, and forecast measures work!! Yay!
My issue now is I now have a table of construction percentages: (unfortunately this medium does not allow me to paste an image, and my .png file does not have a direct url from our sharepoint site that i can refer to here, so I'll summarize):
My table visual (let's call it 'Progress') contains a list of values for Category, SubCategory, and Current Progress, such as:
Category SubCategory Current Progress
Baselay Timberfelling 100%
Baselay ECDs 60%
Baselay Clearing and Grubbing 59%
etc.
I have a separate line chart visual using the S-Curve measures described above to chart 'Actual', 'Forecast', and 'Planned' progress.
The default for the visual is to display progress for Timberfelling.
When the user selects an item from the 'Progress' table such as ECDs, I want the line chart to re-calculate Actual, Forecast, and Planned progress using the selected SubCategory of 'ECDs', and selected 'Asset' of 'REL'. The initial filter on the Line chart visual is Asset = 'REL' which is correct. However, when I select the ECDs form the 'Progress' table, the filters are then associated to the Line Chart visual and have become Asset = 'REL' (correct), Construction Category = 'Baselay' (correct), SubCategory = 'ECDs', SeriesType = 'Act' (ok), and Date = '5/7/2023' (I need to clear date out). The code for the line chart then plots one data point for all three measures (Actual, Forecast, and Planned) as of 5/7/2023. The data points are the correct values for that date but I need to be able to plot ALL date values in order to show a viable Actual, Forecast, and Planned s-curve line chart. HOW DO I DO THIS? lol.
Originally my measure code above used ALLSELECTED to filter the data but no luck, then I tried ALL, then ALLCROSSFILTERED, and then ALLEXCEPT. But none of those options work in clearing the date field from the filters. I need to be able to associate a selection from the Progress table to the s-curve visual and plot its associated planned, actual, and forecasted s-curve values.
Any suggestions would be SO appreciated!! 
Thank you!!
Valerie
Posted : 09/09/2022 10:22 am
Hi Valerie,
Great to hear you're making progress. Well done!
It's very difficult to visualise all this information and help without seeing your file. If you can't share it here, you can email it to me: website at MyOnlineTrainingHub.com
Note: you can upload an image here from your hard drive. It doesn't need to be hosted on OneDrive or similar. Simply add it as an attachment.
Thanks,
Mynda
Posted : 09/09/2022 9:30 pm
