Dynamic Calculated Column Based on User Selection on Date Slicer Selection

[Anonymous]

Hi everyone,

I have run into an issue. I have created measures where I am able to get my desire output but now I need to change these measures into calculated columns because I need append these values into another table. Is it possible to append measures to another table? I can't find any answers for it so now I tried to change these measures into calculated columns but the result is not the same / correct.

I have an example of the my desired output as well as the measures I created to achieve it, can someone guide me through how to change these measures into dynamic calculated columns that get the same result?

From date	To date
4/1/2019	5/1/2019

Location	Date	Count
Seattle	4/1/2019	200
Seattle	4/1/2019	200
Seattle	4/1/2019	150
Seattle	4/1/2019	100
Seattle	4/1/2019	115
Seattle	4/1/2019	110
Seattle	5/1/2019	300
Seattle	5/1/2019	350

Desired outcome is 5 calculated columns to derive the from date based on date slicer, to date, the first count of from date, last count of to date, and the avg count. In this example, the result would look like this:

Location	From Date	To Date	First Count of From Date	Last Count of To Date	Avg Count ((First Count from Date + Last Count To Date) / 2)
Seattle	4/1/2019	5/1/2019	200	350	275

Ultimate goal is to take these calculated columns and append it to another table where I'm taking these counts and merging it with counts of another table. I was able to build this logic using measures but when I try to change them to calculated columns, I don’t get the same results. 

Here are the measures I created:

1) This grabs the min date based on date from slicer (from date)

MIN_DATE_SLICER = CALCULATE(MIN('DIMDATE'[DATE]),ALLSELECTED('DIMDATE'[DATE]))

2) This grabs the max date based on date from slicer (to date)

MAX_DATE_SLICER = CALCULATE(MAX('DIMDATE'[DATE]),ALLSELECTED('DIMDATE'[DATE]))

3) Sum_For_Min_Date = CALCULATE(MAX(CUSTOMER_COUNT[CUSTOMER_COUNT]), FILTER('DIMDATE','DIMDATE'[DATE] = [MIN_DATE_SLICER]))

4) Sum_For_Max_Date = CALCULATE(MAX(CUSTOMER_COUNT[CUSTOMER_COUNT]), FILTER('DIMDATE','DIMDATE'[DATE] = [MAX_DATE_SLICER]))

5) This grabs the first value from the from date

First_Value_From_Min_Date = CALCULATE([Sum_For_Min_Date],FIRSTNONBLANK('DIMDATE'[DATE], [Sum_For_Min_Date]))

6) This grabs the last value from the to date

Last_Value_From_Max_Date = CALCULATE([Sum_For_Min_Date],LASTNONBLANK('DIMDATE'[DATE], [Sum_For_Max_Date]))

Finally.... 

7) AVG_Count = ([First_Value_From_Min_Date] + [Last_Value_From_Max_Date])/ 2

Here is an e.g. of my measures in count cards and a table.... the very bottom table is calculated columns based on the same measure DAX, i am getting circular dependency issues.

[Syndicate_Admin]

Hello!

Do you know if Power BI can generate Pivot Columns?

That is, I have the following case:

Parents	Sales	% Sales
Mex	25	30%
Arg	30	37%
Who	5	6%
Cabbage	15	18%
Come here	7	9%

But when I filtered, for example, Mexico and Argentina, I got the following result

Parents	Sales	% Sales
Mex	25	45%
Arg	30	55%

I need this in column, I don't need it as a calculated measure, since that % of sales I need to put in a Slicer

Thanks a lot

[Syndicate_Admin]

What if you use the percentage of column total? That calculates it naturally.

[Greg_Deckler]

You can base a calculated column on a measure but it won't be dynamic. It will be calculated at the time of data load/refresh and that is it. Calculated columns are not dynamic.

[Anonymous]

Is it possible to somehow capture the values of the measure and append it onto a table? The main roadblock for me is I need to append the values that the measure is giving me to another table and I don't know if its possible in Power BI. 

[V-lianl-msft]

Hi @Anonymous ,

To capture the value of a measure and attach it to a table,you can try to create the right relationship for two tables.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.