Forum Discussion
Solved
Finding values from related tables for Dax and Field parameter values not shown
Hi SergeiBaklan ,
I have 2 queries here:
1st Query:
I have 2 field parameters that filters a visual like below:
The one field parameter acts as a main and other acts as sub.
Main will have only 2 buttons one for target and another for budget.
But sub will have 3 buttons such as Carbon, Units and Cost.
Their relationships are as below:
Now the issue is, when i press the carbon button, it is not displaying any values in the visual as below:
The Actual measure(Show values) that holds this carbon measure as shown below is:
But Actual Units and Actual Cost are showing values as below:
Actual Units:
Actual Cost
2nd Query:
I have a dax measure called Forecast Budget Unit and this is made up of Pre_forecast_BUnit('B' stands for budget and these measure are working perfectly fine!
Now I need a measure like this to calculate 'Forecast Budget Carbon'. When we say carbon, its simply finding the corresponding carbon factor from Factor table(As shown below) & multiplying that with above units.
To give an example on how to find the actual carbon below is the measure:
This is actual carbon, that comes from the Data invoice table, so straightforward.
But for finding out the Forecast budget carbon, we are struggling & need your help.
This involves use of below tables in relationship:
As a extra info, The measure for Forecast Budget Carbon will be similar to 'Forecast Budget Unit' & 'Pre_forecast_BUnit' measures except that we will need to find the carbon factor from factor table for corresponding point id and calendar date/month.
Please help me resolve this sir!
PFA file here Portfolio 6 latest (2) - Copy.pbix
Please let me know if you need further info!
Thanks in advance Sir!
As for the first part. When we select Category Main (Budget or Target or Nothing) we pick-up all categories related to selected main. Thus for the Budget or Target list of categories finally applied for filtering is {Unit, Cost}. There is no Carbon in such list, it appears only Main is not filtered.
With first case filtered Parameter table is empty, Carbon is not selectable and result is blank.
In general, for Carbon calculating we need to remove filter context from Category Main.
In case of parameter table we can't remove shadow filter content since internally DAX makes grouping and we can't get content from the column grouped by other column.
As workaround we may modify ShowValues as
VAR SelectedParameter = SELECTEDVALUE ( 'Parameter 3'[Category] ) VAR nRows = COUNTROWS('Parameter 3')+0 VAR Result = SWITCH ( SelectedParameter, "Units", SUM ( DataInvoice[Units] ), "Cost", SUM ( DataInvoice[Cost] ), "Carbon", [Carbon tCO2e] ) RETURN IF(nRows = 0, [Carbon tCO2e], Result )However, not very reliable from maintenance point of view if we will be modifying parameters configuration.
See results on Page 3
File is here TechComm - OneDrive (live.com)
As for the first part. When we select Category Main (Budget or Target or Nothing) we pick-up all categories related to selected main. Thus for the Budget or Target list of categories finally applied for filtering is {Unit, Cost}. There is no Carbon in such list, it appears only Main is not filtered.
With first case filtered Parameter table is empty, Carbon is not selectable and result is blank.
In general, for Carbon calculating we need to remove filter context from Category Main.
In case of parameter table we can't remove shadow filter content since internally DAX makes grouping and we can't get content from the column grouped by other column.
As workaround we may modify ShowValues as
VAR SelectedParameter = SELECTEDVALUE ( 'Parameter 3'[Category] ) VAR nRows = COUNTROWS('Parameter 3')+0 VAR Result = SWITCH ( SelectedParameter, "Units", SUM ( DataInvoice[Units] ), "Cost", SUM ( DataInvoice[Cost] ), "Carbon", [Carbon tCO2e] ) RETURN IF(nRows = 0, [Carbon tCO2e], Result )However, not very reliable from maintenance point of view if we will be modifying parameters configuration.
See results on Page 3
File is here TechComm - OneDrive (live.com)
Hi SergeiBaklan
Many thanks for your response Sir!
Amazing workaround and a beautiful solution with crisp explanation and wonderful screenshots.
I am always big fan of your patience & deep understanding of technical stuffs.
You are really a super hero for me and am learning a lot from you sir!
I will close this query and mark your solution as best response!
Wish to stay in touch with you sir! wish to stay connected with you on email & linkedin
My email id is email address removed for privacy reasons
Please let me know if you need anything from my end and am happy to help always!
Wish you a good health and happy long life
Excellove15 , you are welcome, glad to help.
