Questions related with context

[anesbbs]

Hi, I'm new to Power BI.

I have dimCust table as follow:

| CustID | CustName | CustAge | CustLoc |

1	A	24	X
2	B	22	Y
3	C	32	X
4	D	16	X
5	E	54	Z
6	F	32	Z
7	G	27	Y
8	H	43	X
9	I	33	Y
10	J	25	Z
11	K	42	Y
12	L	51	X

And Sales table as follow:

| SalesID | CustID | Date | ProductID | Qty |

1	4	01 April 2023	1	2
2	3	02 February 2023	3	2
3	4	13 February 2023	2	4
4	5	14 January 2023	4	3
5	2	13 April 2023	6	12
6	2	18 March 2023	3	3
7	6	21 February 2023	2	5
8	5	19 January 2023	3	6
9	3	17 April 2023	4	3
10	2	09 March 2023	5	10
11	6	22 March 2023	6	4
12	7	17 February 2023	4	10
13	6	19 February 2023	3	9
14	6	28 March 2023	2	7
15	4	21 January 2023	1	2

The relationship is as follow:

I then created a measure table consists of several measures as follow:

1. VisitQty = COUNT('Sales'[SalesID])
2. EarliestVisit = MINX(Sales, [Date])
3. LatestVisit = MAXX(Sales, [Date])
4. PeriodDay = DATEDIFF([EarliestVisit], [LatestVisit], DAY)
5. TotalPeriod = CALCULATE([PeriodDay], ALLSELECTED('dimCust'[CustID]))
6. TotalVisit = CALCULATE(COUNT('Sales'[SalesID]), ALLSELECTED('dimCust'[CustID]))
7. Visit/Period = DIVIDE([TotalVisit], [TotalPeriod])

Then in the report view, I chose Table Visualization and filled it with columns and measures:

• CustID taken from dimCust table
• Other columns are from the above measures

When I excluded CustName column as the above, the last 3 columns consist of fix total value for each column in all rows (red box), as I wanted it.

And as I filtered the CustLoc using slicer, the total values will also be adjusted and each row will still be filled with the fix total value as follows:

But when I included the CustName column, it filters the CustID column and the values in last 3 columns become only for the particular rows, as follows:

One more thing, I added other measure and included it in the table:

1. VV = DIVIDE(([VisitQty] - [Visit/Period]*[PeriodDay])^2, [PeriodDay])

The result for each row is correct, but the total value become zero, as follows:

I have 3 questions:

1. How to show all CustID after add CustName column?

2. How to keep fix total value in each row based on slicer filter after add CustName column?

3. How to show correct total value for VV column?

[v-eqin-msft · Accepted solution]

Hi @anesbbs ,

I suggest you to try code as below.

TotalPeriod = CALCULATE([PeriodDay], ALLSELECTED('dimCust'[CustID]),REMOVEFILTERS(dimCust[CustName]))

TotalVisit = CALCULATE(COUNT('Sales'[SalesID]),  ALLSELECTED('dimCust'[CustID]),REMOVEFILTERS(dimCust[CustName]))

VV Total = IF(HASONEVALUE(dimCust[CustID]),[VV],SUMX(VALUES(dimCust[CustID]),[VV]) )

Result is as below.

Best Regards,

Rico Zhou

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

[anesbbs]

@v-eqin-msft,

There is another question that I need your help, still with the same case.

I added another column to show current date in all rows.

Tried using formula as per your suggestion, but not working: 

CurrDate = CALCULATE(NOW(),  ALLSELECTED('dimCust'[CustID]),REMOVEFILTERS(dimCust[CustName]))

Would you please help again on this one?

[lbendlin]

Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[anesbbs]

This is the outcome that I need:

CustID	CustName	VisitQty	EarliestVisit	LatestVisit	PeriodDay	Total Period	TotalVisit	Visit/Period	VV
1	A
2	B	3	09 March 2023	13 April 2023	35	93	15	0.161	0.20
3	C	2	02 February 2023	17 April 2023	74	93	15	0.161	1.33
4	D	3	21 January 2023	01 April 2023	70	93	15	0.161	0.98
5	E	2	14 January 2023	19 January 2023	5	93	15	0.161	0.28
6	F	4	19 February 2023	28 March 2023	37	93	15	0.161	0.10
7	G	1	17 February 2023	17 February 2023	0	93	15
8	H
9	I
10	J
11	K
12	L
		15	14 January 2023	17 April 2023	93				2.91