Measure using slicer value in two different ways

[M_Ulloa]

Hi data gurus,

I have an issue that I'm hoping one of you may be able to help with. With the following test data:

Initiating Org	Ending Org	Count
A	B	1
B	B	32
C	B	1
D	B	3
E	B	2
F	B	1
B	C	2
B	G	1
B	E	10
B	F	1

I have a slicer on "Ending Org" and two measures:

Measure1: CALCULATE([Count], FILTER(myTable, myTable[Initiating Org] = myTable[Ending Org]))

Measure2: CALCULATE([Count], FILTER(myTable, myTable[Initiating Org] <> myTable[Ending Org]))

These plot correctly on a table visual. The issue is that I want to somehow show where Initiating Org <> Ending Org, but taking into consideration the Initiating Org, so the table visual when filtered for Ending Org B, shows the last 4 lines, where it's the "Initiating Org". I've been able to get the count to work with:

Measure3: CALCULATE([Count], FILTER(ALL(myTable),
myTable[Initiating Org] = SELECTEDVALUE(myTable[Ending Org]) &&
myTable[Ending Org] <> SELECTEDVALUE(myTable[Ending Org]) ) )

But this doesn't plot correctly in a table or matrix visual as it shows the total result (14) in all rows. The expected result would be:

Initiating Org	Ending Org	Count	Measure 1	Measure 2	Measure 3
A	B	1		1
B	B	32	32
C	B	1		1	2
D	B	3		3
E	B	2		2	10
F	B	1		1	1
B	G	1			1
		41	32	8	14

Any help is greatly appreciated! I would have attached a dummy .pbix, but Microsoft doesn't allow such things for "new" users, even though I've been registered in this forum since 2020.

[v-priyankata]

Hi @M_Ulloa 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @M_Ulloa 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @M_Ulloa 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[rohit1991 · Accepted solution]

Hi @M_Ulloa

The issue with your Measure3 returning the same value across all rows stems from the use of SELECTEDVALUE, which doesn't behave as expected when used in a row-level evaluation within a table visual. Instead, you’ll want to leverage the current row context to dynamically reference the “Initiating Org” rather than relying solely on the slicer’s value.

Could you please modify your Measure 3 like this:

Measure3 = 
CALCULATE(
    [Count],
    FILTER(
        ALL(myTable),
        myTable[Initiating Org] = EARLIER(myTable[Initiating Org]) &&
        myTable[Ending Org] = SELECTEDVALUE(myTable[Ending Org]) &&
        myTable[Initiating Org] <> myTable[Ending Org]
    ))

Alternatively, consider using a calculated column if you're looking to simplify row-level logic.

[Irwan]

hello @M_Ulloa 

please check if this accomodate your need.

although i am not sure why you have G in your expected result screenshot if the screenshot showed B value in EndingOrg slicer.

also i am not sure the meaning of measure 3. you want to calculate value when InitiatingOrg is equal to EndingOrg and InitiatingOrg is not equal to EndingOrg. Seems conflicting.

Hope this will help.

Thank you.