dax in drilldown works without relationship

[animebuff]

i have write a simple dax of subtraction and division and I have a region as drilldown

my requirement says to remove the relationship between dim country table and fact table so I removed it using CROSSFILTER 

since I remove the relationship my values for individual region is not right, all region inside a month year shows same value

ex. if month year nov 24 total is 200 

nov 24 asia is 100

nov 24 europe is 25

nov 24 NA is 50

nov 24 LA is 25

after removing the relationship using CROSSFILTER
in drilldown for all region ( asia, europe, NA and LA ), I get 200 

my requirement is to remove country region relationship at the same time the overall dax logic should work on drilldown

[v-hashadapu · Accepted solution]

Hi @animebuff ,Thank you for reaching out to Microsoft Fabric Community Forum.

If the direct relationship between the dimCountry table and the fact table is removed, it will impact the filtering context, so now you see the same total for all regions.

We can use the TREATAS function to create a virtual relationship between the dimCountry table and the fact table. This way, we can eliminate the physical relationship while keeping the correct context for the calculations.

Sample Data:

dimCountry Table

fact Table

Total Value Measure:

Total Value =

VAR SelectedCountries = VALUES(dimCountry[Country])

RETURN

CALCULATE (

    SUM(fact[Value]),

    TREATAS(SelectedCountries, fact[Country])

)

Set up a Matrix Visual:

Sample Output:

If this helps, please mark it ‘Accept as Solution’, so others with similar queries may find it more easily. If not, please share the details.

[v-hashadapu · Accepted solution]

Hi @animebuff , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!

[animebuff]

sure will do it

[v-hashadapu]

Hi @animebuff , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @animebuff , Hope your issue is solved. If it is, can you please consider marking it 'Accept as Solution'. Thank you.

[animebuff]

Requirement:

left side visual is what by default we get in PBI
what I need is right side visual but the values of all region should not be 0.31 rather
apac - 1

europe - 0.5

la - 0.5

afracia - 1

australia - 1

na - 1

uae - 0

only apac, europe and la have both numerator and denominator so it should give respective values

uae only has denominator so it is 0 (0/anything)

rest regions doesn't have data so it should give value as 1

-----------------------------------------------------------------------------------------------------------------------

dax:

default dax = CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success])

modified dax = CALCULATE(CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success]),CROSSFILTER(region[region],'S rate'[region],None))

 ----------------------------------------------------------------------------------------------------------------------

tables

s rate

mk	region	inclusion flag	success
1	apac	y	95
1	europe	n	85
1	la	y	91
1	na	n	97
1	africa	n	88
1	australia	n	95
1	apac	y	93
2	europe	y	94
2	la	n	89
2	na	n	88
2	africa	n	99
2	australia	n	80
2	uae	n	90

region

region

apac

europe

la

na

africa

australia

uae

month

mk	m
1	nov
2	dec

[v-hashadapu · Accepted solution]

Hi @animebuff ,Thank you for reaching out to Microsoft Fabric Community Forum.

If the direct relationship between the dimCountry table and the fact table is removed, it will impact the filtering context, so now you see the same total for all regions.

We can use the TREATAS function to create a virtual relationship between the dimCountry table and the fact table. This way, we can eliminate the physical relationship while keeping the correct context for the calculations.

Sample Data:

dimCountry Table

fact Table

Total Value Measure:

Total Value =

VAR SelectedCountries = VALUES(dimCountry[Country])

RETURN

CALCULATE (

    SUM(fact[Value]),

    TREATAS(SelectedCountries, fact[Country])

)

Set up a Matrix Visual:

Sample Output:

If this helps, please mark it ‘Accept as Solution’, so others with similar queries may find it more easily. If not, please share the details.

[animebuff]

https://community.fabric.microsoft.com/t5/Desktop/dax-relationship-issue-in-drilldown/m-p/4336917#M1...

[v-hashadapu]

Hi @animebuff , as the thread(post) in the link you have provided above is already taken up by another CST member, I am sorry to say i cannot work on it. If my answer to your original post helped your issue, please consider marking it 'Accept as Solution'. If not, please share the details so that we can work together to solve the issue.

[animebuff]

Requirement:

left side visual is what by default we get in PBI
what I need is right side visual but the values of all region should not be 0.31 rather
apac - 1

europe - 0.5

la - 0.5

afracia - 1

australia - 1

na - 1

uae - 0

only apac, europe and la have both numerator and denominator so it should give respective values

uae only has denominator so it is 0 (0/anything)

rest regions doesn't have data so it should give value as 1

-----------------------------------------------------------------------------------------------------------------------

dax:

default dax = CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success])

modified dax = CALCULATE(CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success]),CROSSFILTER(region[region],'S rate'[region],None))

 ----------------------------------------------------------------------------------------------------------------------

tables

s rate

mk	region	inclusion flag	success
1	apac	y	95
1	europe	n	85
1	la	y	91
1	na	n	97
1	africa	n	88
1	australia	n	95
1	apac	y	93
2	europe	y	94
2	la	n	89
2	na	n	88
2	africa	n	99
2	australia	n	80
2	uae	n	90

region

region

apac

europe

la

na

africa

australia

uae

month

mk	m
1	nov
2	dec

[Kedar_Pande]

@animebuff 

Updated DAX Measure

Measure = 
VAR CurrentRegionFilter = 
VALUES(DimCountry[Region]) 
RETURN
CALCULATE(
SUM(FactTable[Sales]), 
TREATAS(CurrentRegionFilter, FactTable[Region]) 
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[animebuff]

more clear version of my requirement

https://community.fabric.microsoft.com/t5/Desktop/dax-relationship-issue-in-drilldown/m-p/4336917#M1...

[animebuff]

requirement:

left side visual is what by default we get in PBI
what I need is right side visual but the values of all region should not be 0.31 rather
apac - 1

europe - 0.5

la - 0.5

afracia - 1

australia - 1

na - 1

uae - 0

only apac, europe and la have both numerator and denominator so it should give respective values

uae only has denominator so it is 0 (0/anything)

rest regions doesn't have data so it should give value as 1

-----------------------------------------------------------------------------------------------------------------------

dax:

default dax = CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success])

modified dax = CALCULATE(CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success]),CROSSFILTER(region[region],'S rate'[region],None))

-----------------------------------------------------------------------------------------------------------------------

tables:

s rate 

mk	region	inclusion flag	success
1	apac	y	95
1	europe	n	85
1	la	y	91
1	na	n	97
1	africa	n	88
1	australia	n	95
1	apac	y	93
2	europe	y	94
2	la	n	89
2	na	n	88
2	africa	n	99
2	australia	n	80
2	uae	n	90

region

region

apac

europe

la

na

africa

australia

uae

month

mk	m
1	nov
2	dec

[Ritaf1983]

Hi @animebuff 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[animebuff]

so left side visual is what by default we get in PBI
what I need is right side visual but the values of all region should not be 0.31 rather
apac - 1

europe - 0.5

la - 0.5

afracia - 1

australia - 1

na - 1

uae - 0

only apac, europe and la have both numerator and denominator so it should give respective values

uae only has denominator so it is 0 (0/anything)

rest regions doesn't have data so it should give value as 1